September 1, 2016
Hi
I have two slicers one called Organisation and the other called Division. When I filter based on Organisation the Division list does not change and shows all divisions and does not filter just the divisions that relate to the Organisation.
I have used the below measure but this does not work.
IF (
COUNTROWS ( Division ) > 0,
IF ( ISBLANK ( SUM ( Data[AUD] ) ), 0, SUM ( Data[AUD] ) )
)
I also have the same measure with USD and then use SWITCH to use a slicer to move between AUD or USD.
I have a table called Data (has columns with AUD, USD, Organisation and Division) and two other tables called Organisation and Division which I have made a relationship with Data.
I have attached a test file where you can see by clicking on Organisation the Division slicer does not change.
Thanks
Jon
July 16, 2010
Hi Jon,
Thanks for sharing a sample file.This makes it a lot easier to quickly pinpoint the problem.
Your measure is returning a zero if there is no count. The Slicer is only ignoring items with 'no data', but zero is considered 'data'.
If you write your formula: =SUM(Data[AUD]) then this results in the Slicers behaving correctly, but I suspect you wrote the formula that way to handle your FX conversion.
Mynda
September 1, 2016
Hi Mynda
Thanks for replying to my question.
The reason for the measure is so that in the Rows section of the pivot table I can show all items even if blank/zero and not just items that have a number.
I have created a P&L in a pivot table, in the Rows I have a list of all P&L accounts and then the Values the measure. So I want to show all P&L accounts no matter if they have a value or not. If I just use the SUM(Data[AUD]) it will only show the P&L accounts with a value and thus does not create the full P&L.
Thanks
Jon
1 Guest(s)