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]))
praveen_k's avatar
praveen_k
Helper I
10 years ago

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's avatar
Greg_Deckler
Community Champion
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]))