Forum Discussion

praveen_k's avatar
praveen_k
Helper I
10 years ago
Solved

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 regu...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    10 years ago

    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's avatar
    Greg_Deckler
    10 years ago

    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]))