Forum Discussion

bolabuga's avatar
bolabuga
Helper V
9 years ago
Solved

Metric for waterfall chart variation

Hello everyone, i would like suggestion for a metric for sum the variation showed on the waterfall chart, i would like to use the chart togheter with a list showing the clients with the most negative results, something like -200 acumulated in 20 months. 

 

This chart is variance by month.

 

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi bolabuga,

     

    According to your description, I have made a sample for your reference.

     

    I assume you have table called "Table1" like below.

     

     

    First, add a "monthNo" column in "Table1".

     

     

    Then you should be able to use the formula below to create a measure to calculate the variation result in a month or several months.

    variation = 
VAR maxMonth =
    MAX ( Table1[monthNO] )
VAR minMonth =
    MIN ( Table1[monthNO] )
RETURN
    CALCULATE ( MAX ( Table1[consume] ), Table1[monthNO] = maxMonth )
        - CALCULATE ( MAX ( Table1[consume] ), Table1[monthNO] = minMonth )

     

    Here is the sample pbix file for your reference.

     

    Regards

3 Replies

  • bolabuga's avatar
    bolabuga
    Helper V
    9 years ago

    I have a sample table trying to explain a little better. 

     

    The idea is use the variation result in a month or several months in a bar chart by client id Vs variation in consume.

     

    • v-ljerr-msft's avatar
      v-ljerr-msft
      Microsoft Employee
      9 years ago

      Hi bolabuga,

       

      According to your description, I have made a sample for your reference.

       

      I assume you have table called "Table1" like below.

       

       

      First, add a "monthNo" column in "Table1".

       

       

      Then you should be able to use the formula below to create a measure to calculate the variation result in a month or several months.

      variation = 
VAR maxMonth =
    MAX ( Table1[monthNO] )
VAR minMonth =
    MIN ( Table1[monthNO] )
RETURN
    CALCULATE ( MAX ( Table1[consume] ), Table1[monthNO] = maxMonth )
        - CALCULATE ( MAX ( Table1[consume] ), Table1[monthNO] = minMonth )

       

      Here is the sample pbix file for your reference.

       

      Regards

      • bolabuga's avatar
        bolabuga
        Helper V
        9 years ago

        Thanks v-ljerr-msft, that solves my question, Nice reply.