Forum Discussion
Waterfall Chart using multiple measures
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
