Forum Discussion
Difference between 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]))
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.
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.
diff = DIVIDE(CALCULATE(SUM([Total]), ALL(Query2), [ReportMo] = MAX([ReportMo]))-SUM([Total]),SUM([Total]))
try that derivative, I'll go test it.