topic Re: How to calculate the difference between one field with two different filters? in DAX Commands and Tips

How to calculate the difference between one field with two different filters?

Anonymous — Mon, 10 Aug 2020 21:28:22 GMT

We have two instances of a fieldon our report (from a single table), each one with its own slicer. We can select a filter value for each instance and display the value, but we're unable to display the difference between the two values.

It seemed that we were getting close once we edited the interaction to make sure that each of the field was only interacting with a single slicer. But the measure that should be returning the value associated with one of the selected filter is not using the measure. We're trying to avoid using a second copy of the data table on the report. Is it possible to accomplish what we're attempting? I'm attaching a simplified version of our sample table and report.

Re: How to calculate the difference between one field with two different filters?

Anonymous — Mon, 10 Aug 2020 21:58:12 GMT

Follow up:

I couldn't attach the file, so here's the link to Dropbox

https://www.dropbox.com/s/d0scgmfl8cjctgh/sample%20problem.zip?dl=0

Re: How to calculate the difference between one field with two different filters?

amitchandak — Tue, 11 Aug 2020 02:32:12 GMT

Anonymous , if they two countries from the same column then try this approach of date , on connected and one disconnected table approach.

https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

Re: How to calculate the difference between one field with two different filters?

AntrikshSharma — Tue, 11 Aug 2020 04:41:26 GMT

Anonymous uploaded the file for you here. https://drive.google.com/file/d/1FU2znT-Oa9_4d3Zpduo4NQXaLZm89o_e/view?usp=sharing

Re: How to calculate the difference between one field with two different filters?

Anonymous — Tue, 11 Aug 2020 14:09:33 GMT

Thank you for the file, but I'm getting an error when I try to open it with Power BI Desktop: "Object reference not set to an instance of an object."

Re: How to calculate the difference between one field with two different filters?

AntrikshSharma — Tue, 11 Aug 2020 14:38:45 GMT

That's wierd, I will just post the measures here.

Country_1_Selected = SELECTEDVALUE( Sheet1[Country] )

Country_2_selected = SELECTEDVALUE( 'Table'[Country] )

Sales1 = VAR Country1 = SELECTEDVALUE ( Sheet1[Country] ) VAR Result = CALCULATE ( SUM ( Sheet1[Sales] ), Sheet1[Country] = Country1 ) RETURN Result

Sales2 = VAR Country2 = SELECTEDVALUE ( 'Table'[Country] ) VAR Result = CALCULATE ( SUM ( Sheet1[Sales] ), Sheet1[Country] = Country2 ) RETURN Result

Sales_Diff = [Sales1] - [Sales2]

Re: How to calculate the difference between one field with two different filters?

Anonymous — Tue, 11 Aug 2020 15:27:14 GMT

Got it.

I understand what you're doing. I suspected we were going to need a copy of the Country table.

I do have one more question about something odd. The two measures for Sum (Sales1 and Sales2) are returning (Blank) in the report. I saw the same problem yesterday, which is why I converted the Sum to a Lookup. Do you see anything that would be preventing the Sum from returning a value? There are no blank rows, and the data type is decimal.