cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
flavourabbit
Frequent Visitor

Waterfall Chart using multiple measures

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,

 

 

temp.PNG

28 REPLIES 28
Anonymous
Not applicable

Over two years later and it looks like this is still impossible for PowerBI... possible for PowerPivot, but not PowerBi. hmm

v-yuta-msft
Community Support
Community Support

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)

  

fact_table.PNG

 

 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 

 

chart_2.PNG

 

 

* FYI, the below is pasted dummy data

 (as xlsx file is encrypted through DRM)

 

YearLocationNet SalesSales ExpenseProduction CostOther OHEBIT

2016UK400-50-200-10050
2016France3000-230-300-502420
2016Germany4000-440-400-503110
2016Belgium2000-1000-500-5000
2017UK600-200-300-1000
2017France4000-600-400-502950
2017Germany5000-600-500-503850
2017Belgium3000-1200-600-500700

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:

捕获.PNG 

Then drag [Attribute] and [Year] to Category, drag [Value] to Y-axis, the result looks like this:

21.PNG 

 

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 Smiley LOL

Let me have a look at it

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors