Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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:
Region | City | Budget | Date - Spend to date | Spend to date | Date - Estimates | Estimates | Total spend | Estimated under/overspend |
London | London | 4,409,549 | Apr 23 - Jun 23 | 0 | Jul 23 - Mar 24 | 0 | 0 | 4,409,549 |
Midlands | Birmingham | 2,118,530 | Apr 23 - Jun 23 | 465,605 | Jul 23 - Mar 24 | 1,988,872 | 2,454,477 | -335,947 |
North West | Manchester | 1,900,905 | Apr 23 - Jun 23 | 508,210 | Jul 23 - Mar 24 | 2,503,343 | 3,011,552 | -1,110,647 |
Scotland | Glasgow | 1,761,437 | Apr 23 - Jun 23 | 131,162 | Jul 23 - Mar 24 | 676,294 | 807,457 | 953,980 |
North East | Newcastle | 1,672,825 | Apr 23 - Jun 23 | 305,055 | Jul 23 - Mar 24 | 1,465,657 | 1,770,712 | -97,888 |
Yorkshire | Sheffield | 1,481,482 | Apr 23 - Jun 23 | 185,195 | Jul 23 - Mar 24 | 951,527 | 1,136,721 | 344,760 |
Yorkshire | Leeds | 1,387,100 | Apr 23 - Jun 23 | 360,393 | Jul 23 - Mar 24 | 1,901,971 | 2,262,364 | -875,264 |
South West | Bristol | 1,368,377 | Apr 23 - Jun 23 | 359,040 | Jul 23 - Mar 24 | 1,683,163 | 2,042,204 | -673,827 |
Screenshot of how this represents in 100% stacked column visualisation:
Thank you in advance of any guidance or advice you can offer.
Solved! Go to Solution.
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.
see attached
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.
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.
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.
Resulting in:
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
72 | |
62 | |
51 | |
48 |
User | Count |
---|---|
206 | |
90 | |
61 | |
59 | |
57 |