Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Bar Visualisation Query for Cost Variance by Category

Hi

I have an interesting request made today. 

Basically the business user wants to use a stacked column visualisation for cost management. He is adamant he doesn't want to use Waterfall visualisation.

There are 2 categories. Budget and Forecast Change. This Forecast can either be a positive or negative amount. Variance is then calculated against Budget. 

For example:

Budget = $1M

Forecast Change= - $250K (negative)

Forecast Budget Spend = $750K

 

In the stacked bar chart  against time they want represent Budget  + Forecast Change together. It works really well if it the Forecast Change is positive, but doesn't work so well if it is negative. Negative amount shows below the axis, which does make sense.

But I am asked to show the negative amount as a bar from the top of the Budget bar. Just like positive amount bar shows from the top of the Budget bar.

This is what it is currently showing, but I need it to show from the top of the Budget bar instead of bottom of budget bar if negative. Any ideas or guidance will be highly appreciated.

Example

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous 

    In addition to sreenub 's reply, we can't change amount<0 to Y axis>0 directly, so we need build measures. I build Four measures to build this visual.

    I build a sample table like yours with three columns: Year, Category, Amount.

    Then I build measures:

    Actual = IF( CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Category]="Forecast"))<0, SUMX(FILTER('Table','Table'[Year]=MAX('Table'[Year])),'Table'[Amount]),CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Category]="Budget")))
    Forecast- = CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Category]="Forecast"&&'Table'[Amount]<0)) *-1
    Forecast+ = CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Category]="Forecast"&&'Table'[Amount]>0))
    Budget = CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Category]="Budget"))
    Total = SUMX(FILTER('Table','Table'[Year]=MAX('Table'[Year])),'Table'[Amount])

    Then build a stack visual as below.(Open data label , set Forecast-'s color = red and Forecast+'s color = Green), add Budget and Total into Tooltips and you can see the values if your mouse hang on the visual.

    You can download the pbix file from this link:Bar Visualisation Query for Cost Variance by Categor

     

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous 

    In addition to sreenub 's reply, we can't change amount<0 to Y axis>0 directly, so we need build measures. I build Four measures to build this visual.

    I build a sample table like yours with three columns: Year, Category, Amount.

    Then I build measures:

    Actual = IF( CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Category]="Forecast"))<0, SUMX(FILTER('Table','Table'[Year]=MAX('Table'[Year])),'Table'[Amount]),CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Category]="Budget")))
    Forecast- = CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Category]="Forecast"&&'Table'[Amount]<0)) *-1
    Forecast+ = CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Category]="Forecast"&&'Table'[Amount]>0))
    Budget = CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Category]="Budget"))
    Total = SUMX(FILTER('Table','Table'[Year]=MAX('Table'[Year])),'Table'[Amount])

    Then build a stack visual as below.(Open data label , set Forecast-'s color = red and Forecast+'s color = Green), add Budget and Total into Tooltips and you can see the values if your mouse hang on the visual.

    You can download the pbix file from this link:Bar Visualisation Query for Cost Variance by Categor

     

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi

      Thank you for the response. I actually found the solution using using pivoting and three custom columns.

      Similar to yours, but slightly different steps.

       

      SJ

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Create separate mesure which converts  -ve value into postive value  and use the new measure for this  visual.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi

      Thanks for your reply.

      That will show as an increase in Forecast. It will be $1M plus $250K. Instead of showing $750K plus $250K.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Use existing measure (which is in -Ve ) for Forecast calculation , the new mesure (Which is converted to +ve ) can be used in the stacked bar chart.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous 

    Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

     

    Best Regards,

    Rico Zhou