Calculate difference of 2 values with different tags, all within the same table and same date

[ar13]

Hi,

So I have data that looks like this: 

To sumarrize the data: so I have daily business days, in which I have data for each location and within each location, I can have up to 3 type of rates. What I am trying to accomplish is reproduce the following (done in Excel) to Power BI: 

It's basically to calculate the difference between each combination of location and type at a particular date. For example, I would want to know the difference between NY-A & CA-A for December 31st, 2019. I would then also like to graph the historical difference between NY-A & CA-A over a selected time period.

The examples I found online revolved around calculating the difference between data of the same category but with different dates (some of examples: https://stackoverflow.com/questions/57710425/find-difference-between-two-rows-by-usind-dax-in-power-... https://community.powerbi.com/t5/Desktop/Calculate-difference-between-two-rows-by-using-Index-column...).  I wanted to know if there was a way to do without having to duplicate the table in the query.

Thanks

[v-kelly-msft]

Hi @ar13 ,

First create 2 dimensional tables as below:

column table = VALUES('Table'[Location_type])

Row table = VALUES('Table'[Location_type])

Then create a measure as below:

Measure = 
VAR _row=CALCULATE(MAX('Table'[rate]),FILTER(ALL('Table'),'Table'[Location_type]=MAX('Row table'[Location_type])&&'Table'[date]=MAX('Table'[date])))
var _column=CALCULATE(MAX('Table'[rate]),FILTER(ALL('Table'),'Table'[Location_type]=MAX('column table'[Location_type])&&'Table'[date]=MAX('Table'[date])))
Return
_row-_column

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[ar13]

Hi Kelly,

Thank you for taking the time to answer. I tried playing with your file and was wondering how to make slicers work in this context. For example, let's say we have the following slicers:

How can I make the slicers work in the data that I have. For example, if I only want to see Type A in the table, it doesn't currently filter out only the A. Also, how would everything work in a dataset that contains multiple dates of data, and a date slicer where only one date at a time is selected.

Thank you!

[v-kelly-msft · Accepted solution]

Hi  @ar13 ,

You need to create another 4 columns as below:

Column-Location = LEFT('column table'[Location_type],2)

Column-Type = RIGHT('column table'[Location_type],1)

Row-Location = LEFT('Row table'[Location_type],2)

Row-Type = RIGHT('Row table'[Location_type],1)

And you will see:

For the related .pbix file,pls see attahched.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!