Dynamic calculation for difference between two non-date matrix columns

[Frenchtom811]

I have a matrix with region related fields as the column headers.  A slicer for this matrix contains about 40 regional related fields available to populate the matrix columns.  I have instructions on the slicer telling end users to select only two regions from within the slicer.  This means any 2 of the 40 available region related fields can serve as coulmn headers.  My rows are populated with item numbers.  Currently my matrix shows the List Price for each item number in both of the chosen regions, see example.  I need a fourth column that calculates the difference between column 2 and column 3 no matter what two regions are selected from the region slicer.  I have tried a formula that relies on the Allselected function and an [Index] but the [Index] value goes unrecognized by Power BI.  

Part number/List Price	Region X	Region Y	Difference (this is the column I need help programming)
ABC123	100.00	100.00	0
ABC456		50.00	50.00
ABC789	25.00	30.00	5.00
DEF123	600.00	500.00	-100.00
DEF456	60.00		-60.00

Here is the calculation that is not working, note the error bar under the [Index] instances.  Should I have a separate column for [Index] or can I define [Index] within my calculation?  Is there a way to define column 2 and column 3 and then just take the difference no matter what the header says for these columns? 

Its frustrating when I can do something in Excel but not PowerBI.

EDIT:  I would ike to add that I am open minded to a solution where a user picks a region from one slicer, a second region from a second slicer and the matrix shows the list prices and any difference.

[kien79]

Hi. Any ideas how to download the file as I cannot do that when clicking on the hyperlink? Thanks

[Ashish_Mathur]

It is an old post.  I do not have that file now.  Please share some data, explain the question and show the expected result.  Either share the download link of the PBI file or share data in a format that can be pasted in an MS Excel file.

[kien79]

I have similar situation. That's why I just wanted to deep dive into your file to see the details. The problem I got when clicking on the hyperlink is that it requests for One Drive sign-in and though I can sign in but it then say

Something went wrong

We're sorry, sign-in isn't working right now. But we're on it! Please try again later.

If this problem persists, contact your support team and include these technical details:

Correlation ID: 47483da1-e0ac-5000-ddec-306238a881db

Date and Time: 7/17/2024 1:17:21 PM

[Ashish_Mathur · Accepted solution]

Hi,

Here's a start.  Download my PBI file from here.

Hope this helps.

[SVM75]

Hi Ashish, i'm facing the same problem but the file does not seem to be there anymore, any chance that you can send me the file or hint the solution? Thanks, SVM

[Ashish_Mathur]

Hi,

It is an old post.  I do not have that file.  Share som data to work with, explain the question and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[SVM75]

Hi Ashish,

I have a list of sale per month per product (example below, in reality, obviosuly several thousands items)

Product	Month	Revenues
Product A	Jan.24	32.90
Product B	Jan.24	78.61
Product C	Jan.24	68.56
Product A	Feb.24	90.98
Product B	Feb.24	65.62
Product C	Feb.24	63.92
Product A	Mar.24	71.93
Product B	Mar.24	56.19
Product C	Mar.24	67.02
Product A	Apr.24	87.79
Product B	Apr.24	32.28
Product C	Apr.24	99.93

and would like to have in PowerBI the option to select two month and have the difference dynamically be calculated, e.g.

THanks, SVM

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[SVM75]

Hi, maybe a stupid question, but I get an error when goes over several years (e.g. compare Feb'23 with Jun'24). What would I need to change? Thanks

[Ashish_Mathur]

Hi,

Will you always select only 2 months - whether within or across years?

[SVM75]

Hi, I guess there would be no way to restrict the user from selecting more than 2 dates, but I could add a comment to advise the user to select only 2 dates

[Ashish_Mathur]

I do not get any error at all

[Frenchtom811]

Hello @Ashish_Mathur , that worked!  It took me some time to figure out that the column subtotals needed to be turned on and relabeled from "Total" to "Difference" but as soon as that was solved it worked great.  Thanks again! 

[Ashish_Mathur]

You are welcome.  If my reply helped, please mark it as Answer.

[akash9519]

Hi @Ashish_Mathur,

This helped a lot for a similar problem I'm facing.
Do you know how to have the sign (+-) as well like it's given in the original question.
The current solution calculate the MAX value -MIN value so the sign gets lost in the calculation.

I would really appreciate your response and guidance!
Thanks

[Ashish_Mathur]

Hi,

This is a very old post.  Share some data, explain the question and show the expected result.

[akash9519]

@Ashish_Mathur,

I've been using the PBI file that you've shared. Using the existing solution the Difference always comes out to be positive, is there a way to determine which Region was selected first and second so that the difference could be calculated using Region1Value - Region2Value?

I want the expected result to have the - sign for negative values(marked in red):

Part number/List Price	Region X	Region Y	Difference (negative value where Region X > Region Y)
ABC123	100.00	100.00	0
ABC456		50.00	50.00
ABC789	25.00	30.00	5.00
DEF123	600.00	500.00	-100.00
DEF456	60.00		-60.00

[Ashish_Mathur]

We cannot subtract based on order of the selection.

[lbendlin]

When you say "difference"  do you mean "difference based on the alphabetical order of the two regions picked by the user regardles which region they picked first" ?

DAX can tell you which filters are applied but it has no idea in which sequence they were applied.

[Frenchtom811]

Hello @lbendlin and thanks for your reply.  I need any difference between the two columns, the alphabetical order and the choice sequence order are irrelevant.  I will only need the difference between two columns, there will never be 3 or more coulmns.

Thanks!