Forum Discussion

bolabuga's avatar
bolabuga
Icon for Helper V rankHelper 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...
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

bolabuga's avatar
bolabuga
Icon for Helper V rankHelper 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
    Icon for Microsoft Employee rankMicrosoft 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
      Icon for Helper V rankHelper V
      9 years ago

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