Difference between Columns dynamically

[praveen_k]

I have a requirement to calculate the percentage difference between columns 1,2,3,..., etc dynamically based on user selection on the report. These columns represent months and increase regularly. 

I created a measure to calculate between two columns :

Diff = DIVIDE(SUM(Query1[3])-SUM(Query1[2]),SUM(Query1[2]))

If have a slicer on the report with column names and want to calculate the percentage difference based on user selection, how can we do this ? Is this possible in Power BI ? 

Example - 

Month slicer - 1,2,3

when the user selects option 2, the difference that need to be calculated is 

Diff = DIVIDE(SUM(Query1[3])-SUM(Query1[2]),SUM(Query1[2]))

when the option 1 is selected, the difference that need to be calculated is - 

Diff = DIVIDE(SUM(Query1[3])-SUM(Query1[1]),SUM(Query1[1]))

When a extra column 4 is added in the month end; the measure should be able to pick that as the latest and compare with other columns dynamically.

[Greg_Deckler · Accepted solution]

OK, I actually tested this one. Had my ALL clause in the wrong spot:

diff = DIVIDE(CALCULATE(SUM([Total]), FILTER(ALL(ReportMo),ReportMo[ReportMo] = MAXX(ALL(ReportMo),[ReportMo])))-SUM([Total]),SUM([Total]))

[Greg_Deckler]

I'm thinking that you are going to have to unpivot your "month" columns so that you have a column that has month in it. Then, you could FILTER in your formulas based upon the MAX of that column. @Sean and I just went through a similar thing with someone here:

http://community.powerbi.com/t5/Desktop/Filter-on-the-measure/m-p/31907

[praveen_k]

I removed the pivot table and here is how the original table looks like. 

I am not sure how to proceed after this is my first trial in BI. 

[Greg_Deckler]

OK, I am thinking something like putting a slicer on the report for ReportMo and then having a measure like:

Diff = DIVIDE(CALCULATE(SUM([Total]),ALL(Table),MAX([ReportMo]))-SUM([Total]),SUM([Total]))

If I did that right, what should happen is that the measure takes the SUM of all rows with a MAX reportmo (regardless of what is selected), you might need an ALLEXCEPT([PersonID]) instead of ALL and then the rest of the calculations are filtered by your slicer.

[praveen_k]

Thanks for the code smoupre. 

diff = DIVIDE(CALCULATE(SUM([Total]), ALL(Query2), MAX([ReportMo]))-SUM([Total]),SUM([Total]))

This measure came up with an error - 

A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

[Greg_Deckler]

diff = DIVIDE(CALCULATE(SUM([Total]), ALL(Query2), [ReportMo] = MAX([ReportMo]))-SUM([Total]),SUM([Total]))

try that derivative, I'll go test it.

[praveen_k]

This is what I get now. Am I missing something or doing it wrong ?

[Greg_Deckler]

OK, this should get you there without errors. This is why I shouldn't try to write DAX without testing it. Needed the FILTER clause.

diff = DIVIDE(CALCULATE(SUM([Total]), ALL(ReportMo), FILTER(ReportMo,ReportMo[ReportMo] = MAX([ReportMo])))-SUM([Total]),SUM([Total]))

[praveen_k]

This give me a value of 0 when ReportMo 3 is compared with ReportMo 2, which is incorrect.

The measure used is - 

diff2 = DIVIDE(CALCULATE(SUM([Total]), ALL(Query2[ReportMo]), FILTER(Query2,Query2[ReportMo]= MAX([ReportMo])))-SUM([Total]),SUM([Total]))

[Greg_Deckler · Accepted solution]

OK, I actually tested this one. Had my ALL clause in the wrong spot:

diff = DIVIDE(CALCULATE(SUM([Total]), FILTER(ALL(ReportMo),ReportMo[ReportMo] = MAXX(ALL(ReportMo),[ReportMo])))-SUM([Total]),SUM([Total]))

[praveen_k]

Thanks a lot! This works perfect. My next step now is how can we extend the filter to another column? 

diff2 = DIVIDE(CALCULATE(SUM([Total]), FILTER(ALL(Query2),Query2[ReportMo] = MAXX(ALL(Query2),[ReportMo])))-SUM([Total]),SUM([Total]))

Is this doable?

[Greg_Deckler]

Sorry that was a bit painful. But we got there in the end. Not sure what you are referring to for your next question, can you explain it a little more?

[praveen_k]

All credits to you!

I have another column value that i would like to filter and caluclate the percentage difference. 

Ex- 

I have another column - Level. 

When I select Report Mo - 2 and Level - High; I should see the percentage difference between months 3 and 2 where Level = High.

I am able to see the some value being caluclated, but it is obviously wrong. Here is what i see on report - 

 And the table looks like this - 

[Greg_Deckler · Accepted solution]

@praveen_k and @Sean - Here is the revised formula that accounts for Level, basically replace the ALL clauses with ALLEXCEPT and get ALL of the rows with the exception of those filtered by Level.

diff2 = DIVIDE(CALCULATE(SUM([Total]), FILTER(ALLEXCEPT(ReportMo,ReportMo[Level]),ReportMo[ReportMo] = MAXX(ALLEXCEPT(ReportMo,ReportMo[Level]),[ReportMo])))-SUM([Total]),SUM([Total]))

[praveen_k]

Thanks @Greg_Deckler!

[Greg_Deckler]

No problem @praveen_k, glad we got it figured out!

[Sean]

@praveen_k or @Greg_Deckler I followed along with this example but I can't figure out what diff2 represents?

What does it help you see in the data? Did I construct my data set properely? Thanks!

[Sean]

@praveen_k You may be able to do this with a Parameter Table

http://www.daxpatterns.com/parameter-table/