locked
Slicer values got affected by calculated measure RRS feed

  • Question

  • Hello,

    Due to the confidential of the data, I can't shared the workbook.

    However, let me try and described my problem as clear as possible.

    I am creating an Income Statement with Rows being the regular income statement headers such as 'Revenue', 'Expense', 'Net Income' etc, with some metrics in the power pivot.

    Assume I have the following hierarchy created. Country, Province and City, and I put this hierarchy fields in the Column of the power pivot.

    Without having any measures created, all of my slicers have all the values shown.

    i.e. For Country - USA, Canada etc

    i.e. For Province - California, Illinois, Alberta, Ontario etc

    i.e. For City - Los Angeles, Chicago, Calgary, Toronto etc

    There is 1 specific metric that only affects say 'Toronto', and thus I created a measure for it.

    And once I put together a 'combined' calculation measure and put it under VALUES in the power pivot, all of my slicers only shows a specific value (Country slicer only shows 'Canada', Province slicer only shows 'Ontario' and City slicer only shows 'Toronto'), ALTHOUGH my columns still shows 'USA' and 'Canada' etc with their respective 'Province' and 'City' values in the Column when I expanded.

    I know that on the slicer, I can go to the setting and 'check' the box that says 'Visually indicate items with no data', but that will defeat the purpose as if users say only interested in the specific province for example, user can select on it and have other slicers 'follow' and hide 'Country' and 'City' that are not related to the user specified city.

    I tried removing that 1 particular metric and my slicers value shows correctly as how the values in the COLUMNS of the pivot table.

    May I know how can I resolve this issue?

    Monday, January 14, 2019 4:10 PM

All replies