Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
ACEvans
Frequent Visitor

Representing overspends above 100% in a 100% stacked column chart

Good morning everyone,

 

First time poster and novice Power BI user so take it easy on me, I’m still learning 😉

 

First things first:

Power BI Desktop Version: 2.120.731.0 64-bit (August 2023)

I’ve googled, searched on this forum and asked ChatGPT but can’t seem to find a solution to produce a visualisation the way my audience and I want to view.

 

Requirement: visualisation showing budget utilisation consisting of values of spend to date, estimates and Estimated under/overspend. In this case, ideally, we want cities to spend 100% of their budget so it seemed sensible to visualise this in a 100% stacked column chart.

 

Unfortunately, perhaps because of the underlying data and/or my immaturity in data modelling, overspends are represented as negative percentage values (-%). This is counter intuitive for my audience when it is desired for overspends to be represented as 110%, for example.

 

Additionally, in the case of columns with overspends, the percentage values represent a % of the forecasted spend (including overspend) rather than of the 100% budget value.

 

Example data used in the model:

 

RegionCityBudgetDate - Spend to date Spend to dateDate - EstimatesEstimatesTotal spendEstimated under/overspend
LondonLondon4,409,549Apr 23 - Jun 230Jul 23 - Mar 24004,409,549
MidlandsBirmingham2,118,530Apr 23 - Jun 23465,605Jul 23 - Mar 241,988,8722,454,477-335,947
North WestManchester 1,900,905Apr 23 - Jun 23508,210Jul 23 - Mar 242,503,3433,011,552-1,110,647
ScotlandGlasgow1,761,437Apr 23 - Jun 23131,162Jul 23 - Mar 24676,294807,457953,980
North EastNewcastle1,672,825Apr 23 - Jun 23305,055Jul 23 - Mar 241,465,6571,770,712-97,888
YorkshireSheffield1,481,482Apr 23 - Jun 23185,195Jul 23 - Mar 24951,5271,136,721344,760
YorkshireLeeds1,387,100Apr 23 - Jun 23360,393Jul 23 - Mar 241,901,9712,262,364-875,264
South WestBristol1,368,377Apr 23 - Jun 23359,040Jul 23 - Mar 241,683,1632,042,204

-673,827

 

Screenshot of how this represents in 100% stacked column visualisation:

ACEvans_0-1692873756900.png

 

 

Thank you in advance of any guidance or advice you can offer.

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

we want cities to spend 100% of their budget so it seemed sensible to visualise this in a 100% stacked column chart.

No. A regular stacked column chart is more appropriate. But you need to do a bit of work to make it look pretty.  You need to stack:
- the spend to date

- the estimated spend up to the budget  (or lower if underspend) 

- the underspend if applicable

- the overspend if applicable.

 

lbendlin_0-1693003000817.png

 

see attached

View solution in original post

6 REPLIES 6
lbendlin
Super User
Super User

we want cities to spend 100% of their budget so it seemed sensible to visualise this in a 100% stacked column chart.

No. A regular stacked column chart is more appropriate. But you need to do a bit of work to make it look pretty.  You need to stack:
- the spend to date

- the estimated spend up to the budget  (or lower if underspend) 

- the underspend if applicable

- the overspend if applicable.

 

lbendlin_0-1693003000817.png

 

see attached

Thank you for your input and suggestion. It's so obvious now I've seen it that stacked column is the best solution, the third stack being under or overspend knowing that both can't exist at the same time. 

 

When I use your expression for calculated column headed 'Overspend', my version of PowerBI errors on 'Spend[Total spend]' as show in the screenshots below which I don't understand. 

 

PBI error.PNG

 

PBI error2.PNG

Remove the table name part. Not required for calculated columns. 

Thanks - I thought as much and that's what I did.

 

The data I provided in my table above is an excerpt from a larger dataset. As a novice with DAX, I'm currently working through the formula in Excel before translating to PBI/DAX because your expression for 'Overspend' leaves blank values in some cases in my larger dataset where 'Estimate' plus 'Spend' do not equal 1. 

 

blanks.PNG

 

Resulting in:

chart.PNG

 

P.s., I know I need to learn DAX syntax but for me, at the moment at least, it's easier for me to work out the formula to get the expected output in Excel, before I convert/translate into DAX so that I can validate the output in Power BI. I don't have the level of trust in my DAX competency yet to write in DAX and validate in-place 😞 It will come with time 

 

 

 

 leaves blank values in some cases in my larger dataset where 'Estimate' plus 'Spend' do not equal 1

That would mean your "Total spend"  column has inconsistent data?


@lbendlin wrote:

 

 leaves blank values in some cases in my larger dataset where 'Estimate' plus 'Spend' do not equal 1

 

That would mean your "Total spend"  column has inconsistent data?


I don't think it's that as it's a simple SUM function in the Excel data loaded in. I have moved forward in my investigations, probably got ahead of you and reported my own erroneous results. I'm sorry. Now, I can't replicate how/when/why I was receiving blanks in overspend when 'TB' >1.

 

The problem I was trying to resolve is as attached (.pbix) which I encountered when loading in a larger range of data. As you will see, using your expressions, in particular the MIN function in 'Estimate', there are some occurrences (Birmingham, Milton Keynes, Newcastle, Northampton, Southampton, Teeside, Wigan) where 'TB' clearly shows overspend with a >1 value, yet value is returned by IF in 'Underspend', not 'Overspend' as would be expected. This is caused by MIN in 'Estimate'. I will do a little more investigation tomorrow, although I have found a workaround which I am satisfied with, if not as perfect and elegant as what you initially produced.

I feel bad for using (wasting) so much of your time troubleshooting my perhaps haphazard and inexperienced approach to this. Apologies, and thank you. My only consolation is that another novice might find this thread on a Google search in six months’ time and find it helpful.

 

P.s. seems I'm not able to attach the .pbix file, for some reason... perhaps I don't have enough reputation on the forums yet for this function. OneDrive link here: spend2.pbix

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.