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 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.
This is what I get now. Am I missing something or doing it wrong ?