Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hello,
I know this must've been an old topic in Business Intelligence.
However, I didn't find any idea to realize what I'd like to achieve.
The thing is I'd like to create a Waterfall Chart with multiple measures.
Let's say that there's Fact Table containg accounting data (Revenues, Costs, Profits)
Thus it's a kind of running total, I can expect to draw the chart.
(Which item is main cause of profit decrease e.g., Ads/Logistics)
However, only solution I found at Google is restructuring the dataset like below
I don't think it's a good idea to manipulate the fact table, plus if I create another table, all the current measure refering the Table A would be not working with newly made one.
Is there any way to solve this?
Many thanks,
You can actually create a new table containing the names of your measures (You can write them manually). You can then use this table as your category in your waterfall and create a new measure for your Y axis. My measure looks like ; WF Measures = VAR Meas = SELECTEDVALUE('Waterfall 2'[Column1]) return SWITCH(TRUE(); Meas = "Ending Inventory (LY)" ;1 ;Meas = "Beginning Inventory (Feb)";-1 ; Meas = "Production" ; 1; Meas = "Sales" ; -1;Meas = "Interco & Adjustments *" ;4 ;Meas = "Ending Inventory (Act)" ; 1; -10) **You can replace the numbers with the measure you want it to calculate**
I'm having the exact same issues as well with multiple measures not working in the Waterfall visual. I was hoping that there would have been a solution by now but I guess not 😞
I created a mock up of the data so I could show the database guys what I was trying to achieve and it doesn't look like it's possible without creating a completley new table with the specific purpose of providing this visual - which is probably not going to happen.
Over two years later and it looks like this is still impossible for PowerBI... possible for PowerPivot, but not PowerBi. hmm
Hi flavourabbit,
"
However, only solution I found at Google is restructuring the dataset like below
I don't think it's a good idea to manipulate the fact table, plus if I create another table, all the current measure refering the Table A would be not working with newly made one.
"
<--- Could you please clarify more details or give a demo about your fact table, Table A, current measures and expected result?
Regards,
Jimmy Tao
Hello,
I've made some dummy data to explain my situsation.
Below is the fact table (originally the # of column is much larger than this)
With measures like SumofNetSales or SumofSalesExpense, I'd like to draw the below waterfall chart in Power BI.
This is because I'd like to compare which account had a effect on overall profit between year 2016 and 2017.
Do you know how can I materialize the waterfall chart in Power BI
* FYI, the below is pasted dummy data
(as xlsx file is encrypted through DRM)
YearLocationNet SalesSales ExpenseProduction CostOther OHEBIT
2016 | UK | 400 | -50 | -200 | -100 | 50 |
2016 | France | 3000 | -230 | -300 | -50 | 2420 |
2016 | Germany | 4000 | -440 | -400 | -50 | 3110 |
2016 | Belgium | 2000 | -1000 | -500 | -500 | 0 |
2017 | UK | 600 | -200 | -300 | -100 | 0 |
2017 | France | 4000 | -600 | -400 | -50 | 2950 |
2017 | Germany | 5000 | -600 | -500 | -50 | 3850 |
2017 | Belgium | 3000 | -1200 | -600 | -500 | 700 |
Hi flavourabbit,
Click Editor Queries, click on [Net Sales], [sales Expense], [Production Cost], [Other OH], [EBIT], then click Transform-> Unpivot Columns, table after transforming is like below:
Then drag [Attribute] and [Year] to Category, drag [Value] to Y-axis, the result looks like this:
Regards,
Jimmy Tao
Thanks for the reply.
However, unlike the example, the fact table is quite hugh (1M rows, 200 columns) and also has a lot of measures based on the columns. so I think if I create a table with query editor only dedicated for the waterfall chart, then it would be a loss...
(resulting in slow performance and large file size)
Is there any workaround?
(it would be nice to recognize measure names as categorical field contents and use their value)
Hi there,
Have you found a solution for this since I am having the same issue where I have to create a waterfall chart from created measures?
Thanks
Hi,
The only workaround I know is using R script (or Python)
I followed the following article's instruction. (basically it's using ggplot2's rect function)
https://analyticstraining.com/waterfall-charts-using-ggplot2-in-r/
I hope it helps
Hi,
Thank for your reply
Let me have a look at it
User | Count |
---|---|
128 | |
73 | |
70 | |
58 | |
53 |
User | Count |
---|---|
193 | |
96 | |
66 | |
62 | |
52 |