Bar Visualisation Query for Cost Variance by Category

[Anonymous]

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

[v-rzhou-msft]

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

[v-rzhou-msft · Accepted solution]

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]

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

[sreenub]

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

[Anonymous]

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.

[sreenub]

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.