Forum Discussion
Difference between Columns dynamically
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.
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 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]))
17 Replies
praveen_k You may be able to do this with a Parameter Table
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
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.
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.
