cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bswylie Regular Visitor
Regular Visitor

ALL Function - allow filter for columns but still apply ALL for a specific measure

I seem to have a situation where I both need to apply a filter based on a column called "metric", but also have to encapsulate the measure to not filter the source table based on that same column. Let me demonstrate... (it feels like a really noob question but I'm just not able to think the right way around this, so would be really grateful for any pointers...)

 

I've loaded a file to... https://dl.dropboxusercontent.com/u/78036039/Slicer%20with%20Measures%20%26%20Columns.pbix

 

Consider a simple measure being

 

Profit = Revenue - Costs

 

The source table looks something like this (data like this by necessity):

 

Source.PNG

 

 

 

 

 

 

 

 

 

 

So my calculation for the measure [Profit] is:

 

Profit = CALCULATE(SUM(SourceData[Value]), FILTER('SourceData', [Metric] = "Revenue")) - CALCULATE(SUM(SourceData[Value]), FILTER('SourceData', [Metric] = "Costs"))

 

This works great - until I have a dashboard where I filter visuals using a slicer on either revenue or costs:

 

Costs Selected.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

As you can see - when I select "costs" in the slicer, the profit is calculated as P = [0] - [Costs], because the revenue is filtered out from the source table (giving a value of zero), and profit is simply negative costs.

 

What I'd like to obviously have is to filter the table to update the top visual (show either revenue or costs in top graph) but apply the ALL function for the Profit measure.

 

I've tried something like the following (which feels intuitively incorrect anyway, and doesn't work either):

 

Profit = CALCULATE(SUM(SourceData[Value]), FILTER('SourceData', [Metric] = "Revenue"), ALL('SourceData'[Metric])) - CALCULATE(SUM(SourceData[Value]), FILTER('SourceData', [Metric] = "Costs"), ALL('SourceData'[Metric]))

 

What's the correct way of handling a situation like this? Again, sorry for the newbie question.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Datatouille Established Member
Established Member

Re: ALL Function - allow filter for columns but still apply ALL for a specific measure

@bswylie

 

Since your slicer comes from Dim_Metric Table, you have to apply your filter on Dim_Metric Table to benefit from filter propagation !

 

[Costs]= CALCULATE( [Total Amount]  , Dim_Metric[Metric] = "Costs")

[Revenue] = CALCULATE( [Total Amount] , Dim_Metric[Metric] = "Revenue")

 

Please accept as a solution so everyone can benefit from our discussion !

View solution in original post

4 REPLIES 4
Highlighted
Datatouille Established Member
Established Member

Re: ALL Function - allow filter for columns but still apply ALL for a specific measure

Hi @bswylie

 

There is no newbie question ! 

In this case, you don't need to use filter function.

 

Try this:

[Total Amount]= SUM(Sales[Value])

 

[Costs]= CALCULATE( [Total Amount]  , Sales[Metric] = "Costs")

[Revenue] = CALCULATE( [Total Amount] , Sales[Metric] = "Revenue")

[Profit] = [Revenue] - [Costs]

 

Place [Total Amount] in the "value" section of your first chart - It will automaticaly adjust to filter context and display revenue or costs (based on the slicer selection).

Place [Profit] in the "value" section of your second chart.

 

Why is this working ?

Because CALCULATE ( [Total Amount] , Sales[Metric] = "Costs") is equivalent to

CALCULATE( [Total Amount] , Filter( All(Sales[Metric]) , Sales[Metric] = "Costs")

It overrides the user's choice (e.g ignores filter context) and forces Sames[Metric] to be equal to "Costs" no matter what the user has chosen (or not chosen).

{Same logic for [Revenue] Measure with "Revenue" filter}

 

As @MattAllington suggests here, I broke my measures into interim parts. It makes it easier to read, understand and update your calculations.

bswylie Regular Visitor
Regular Visitor

Re: ALL Function - allow filter for columns but still apply ALL for a specific measure

Hi @Datatouille

 

Thanks so much for the response.

 

Unfortunately this doesn't seem to work? I still get the same outcomes when selecting either Revenue or Cost metric in the slicer.

 

https://dl.dropboxusercontent.com/u/78036039/Slicer%20with%20Measures%20%26%20Columns%20-%20v2.pbix

 

Cheers,

Brian

 

 

Datatouille Established Member
Established Member

Re: ALL Function - allow filter for columns but still apply ALL for a specific measure

@bswylie

 

Since your slicer comes from Dim_Metric Table, you have to apply your filter on Dim_Metric Table to benefit from filter propagation !

 

[Costs]= CALCULATE( [Total Amount]  , Dim_Metric[Metric] = "Costs")

[Revenue] = CALCULATE( [Total Amount] , Dim_Metric[Metric] = "Revenue")

 

Please accept as a solution so everyone can benefit from our discussion !

View solution in original post

bswylie Regular Visitor
Regular Visitor

Re: ALL Function - allow filter for columns but still apply ALL for a specific measure

@Datatouille

 

Thanks so much, this works great. Really appreciate it.

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 134 members 1,606 guests
Please welcome our newest community members: