Power BI - Difference of a measure compared to Selection

[sriram74]

Good Afternoon,

We have a location and a sales table. When the location is selected, it should list all values in table with a measure showing the difference of the sales based on the selection.

Country	Sales
USA	100
UK	90
Canada	85
Mexico	110

When the slicer value of  'USA' (location) is selected. The result should be 

Country	Sales	Diff
USA	100	0
UK	90	10
Canada	85	15
Mexico	110	-10

Where diff = Sales - ( Sales of the selected Country)

Thank You in advance for making the time to go through my post.

-Sriram

[v-yuta-msft · Accepted solution]

@sriram74 ,

You need to create another tabel "Selection" which has only one [Country] column. Then you can create the slicer based on the "Selection" table.

 

Then create a measure using DAX below:

Diff = 
VAR Selected_Country = SELECTEDVALUE('Selection'[Country])
VAR Selected_Country_Sales = CALCULATE(MAX('Table'[Sales]), FILTER(ALL('Table'), 'Table'[Country] = Selected_Country))
VAR Current_Country_Sales = MAX('Table'[Sales])
RETURN
Selected_Country_Sales - Current_Country_Sales

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sriram74]

Hello v-yuta-msft,

This worked. Thank You!

-Sriram

[KHorseman]

Diff = CALCULATE( [Sales], ALL(NameOfTableHereTheCountryColumnIs)) - [Sales]

[Levajar]

You can use SELECTEDVALUE() function too 

https://www.sqlbi.com/articles/using-the-selectedvalue-function-in-dax/

https://community.powerbi.com/t5/Desktop/Use-selectedvalue-in-a-measure/td-p/483456 

Hope it helps!

[sriram74]

Thank you both for making the time.

I am not sure where I am wrong. But i am not able to get the results i need.

Using KHorsemans suggesion, 

Diff = CALCULATE( sum([Sales]), ALL(Table2[country])) - sum(Table1[Sales])

i was able to get the list of all countries but the sales column showed only for the country selected. The difference hence was incorrect.

Using Levajar's suggestion of using Selected value,

Diffs = var _country = selectedvalue(Table2[country]) return sumx(filter(table1,Table1[country] = _country),[sales])

i was not able to get the list of all the countries and thier respective sales. Not sure if I misunderstood your suggestions.

Thanks Again!