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

 

This give me a value of 0 when ReportMo 3 is compared with ReportMo 2, which is incorrect.

 

The measure used is - 

 

diff2 = DIVIDE(CALCULATE(SUM([Total]), ALL(Query2[ReportMo]), FILTER(Query2,Query2[ReportMo]= MAX([ReportMo])))-SUM([Total]),SUM([Total]))

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

    Thanks a lot! This works perfect. My next step now is how can we extend the filter to another column? 

     

    diff2 = DIVIDE(CALCULATE(SUM([Total]), FILTER(ALL(Query2),Query2[ReportMo] = MAXX(ALL(Query2),[ReportMo])))-SUM([Total]),SUM([Total]))

     

     

     

     

    Is this doable?

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

    Sorry that was a bit painful. But we got there in the end. Not sure what you are referring to for your next question, can you explain it a little more?

  • 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 - 

     

     

     

     

  • Sean's avatar
    Sean
    Community Champion
    10 years ago

    praveen_k or Greg_Deckler I followed along with this example but I can't figure out what diff2 represents?

    What does it help you see in the data? Did I construct my data set properely? Thanks!

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    10 years ago

    No problem praveen_k, glad we got it figured out!