cancel
Showing results for
Did you mean:
bswylie 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): 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: As you can see - when I select "costs" in the slicer, the profit is calculated as P =  - [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

## 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 !

4 REPLIES 4
Highlighted
Datatouille 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

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

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

## 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 !

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

Announcements #### 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 was a busy month in the community. Read the recap article to learn about some of the events and content.  #### Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI. Top Kudoed Authors
Users Online
Currently online: 134 members 1,606 guests
Recent signins:
• barron2020 • Azat • ppodgorski • jstacruz • vvmob • RitaP_123 • intelabhinav • Zabee • sirestituto • bhensley • thisisjunhong • WJanczak • fauzidj • matthewway 