cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rich
New Member

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...

1 REPLY 1
konstantinos
Memorable Member
Memorable Member

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 

 

Untitled.png

 

 

 

 

Konstantinos Ioannou

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.