Forum Discussion

Rich's avatar
Rich
Advocate I
10 years ago

Stop ALL function being filtered by a slicer

Feeling like I am missing something simple here...

 

I'm using the ALL function with a simple sum measure to stop the filtering of the 'Status' column:

 

Cost = Sum('Cost table'[Cost])

 

All Cost = [Cost](ALL('Cost table'[Status]))

 

However if I have a slicer for 'Status' on the page it will still filter the measure according to the slicer.

 

E.g. 

 

Cost      Status

10          Live

10          Closed

 

If I have a slicer for Status on the page and select only 'Live', I would expect the out come of the formula to still be 20, however I am getting 10.

 

Anybody know what I am missing?

 

The out come i am trying to achieve in the above scenario would be to display a card with the result of 50% i.e. [cost]/[all cost] = 10 / 20 = 50%, which cannot be done by simply turning off cross filtering in the 'Edit Interactions' menu...

Need Help

1 Reply

  • konstantinos's avatar
    konstantinos
    Memorable Member
    10 years ago

    Your formulas are correct but...the cost formula that you have in the table behaves as it suppose to be. If you add the formula All cost in the table it should show 20..The are different formulas..so the devide will be Cost = 10 /  All Cost = 20 result = 0,5 or you can combine it to one

     

    One formula

     

    Cost Ratio = Var Cost = SUM('Cost Table'[Cost])

Var AllCost = CALCULATE( SUM('Cost Table'[Cost]);ALL('Cost Table'[Status]))
Return

DIVIDE( Cost ; AllCost )

     

    With three measures 

    Cost = SUM('Cost Table'[Cost])



All Cost = CALCULATE( SUM('Cost Table'[Cost]);ALL('Cost Table'[Status]))

Cost Ratio = [Cost] / [All cost ]

or if you expect zero values on cost 

Cost Ratio = Divide ( [Cost];[All Cost])

    ( I am using semicolon which you might use dot )

     

     

    Either way is correct..and the slicer selection on status don't affect the result