Forum Discussion

afp141991's avatar
afp141991
Frequent Visitor
8 years ago

Funnel & Waterfall Chart

Hello, I am trying to design the following chart using Power BI:     As you can see, I have 500 units in the category 1 that are divided in 6 sub-categories. Then, the category 1-6 (400 uni...
Tips and Tricks
v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

Hi afp141991,

 

Can you organize your data in the format like below?

Class1	Class2  	Value
Cat.1-1	Cat.1-1 	40
Cat.1-2	Cat.1-2 	40
Cat.1-3	Cat.1-3 	1
Cat.1-4	Cat.1-4 	15
Cat.1-5	Cat.1-5 	4
Cat.1-6	Cat.1-6-1	125
Cat.1-6	Cat.1-6-2	250
Cat.1-6	Cat.1-6-3	25

Then you can create a Waterfall Chart like this and open the Drill Mode. When you click the column, it will open the next level visual.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

afp141991's avatar
afp141991
Frequent Visitor
8 years ago

Hi Dale,

 

Thank you for your answer. However, the result obtained with your data model is not what I am looking for, because I need to see all tha data at the same level without the necessity to expand the category 1.6. If I use your approach and show all the data, I obtian the following result:

 

 

 

And this is not what I am looking for. I have been looking for a solution, and I have tried different alternatives but I think that nowadays there is no option to draw my graph using Power BI tools. If anyone knows how to do this kind of graph, please, let me know.

 

Thank you

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi afp141991, 

     

    Maybe R script can do this work. 

    1. Organize your data like this. 

    id	Class1  	Class2	Value	Start	End
1	Cat.1   	Total	500	0	500
2	Cat.1-1 	Chid	40	500	460
3	Cat.1-2 	Chid	40	460	420
4	Cat.1-3 	Chid	1	420	419
5	Cat.1-4 	Chid	15	419	404
6	Cat.1-5 	Chid	4	404	400
7	Cat.1-6 	Total	400	0	400
8	Cat.1-6-1	Chid	125	400	275
9	Cat.1-6-2	Chid	250	275	25
10	Cat.1-6-3	Chid	25	25	0

    2. Create a R visual with this code.

    library(ggplot2)
ggplot(dataset, aes(Class1, fill = Class2)) + geom_rect(aes(x = Class1, xmin = id - 0.45, xmax = id + 0.45, ymin = Start, ymax = End), position  = 'dodge') + geom_text(aes(label = Value, y =  510),position = position_dodge(0.9), vjust = 0)

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    You can try it in this file: https://1drv.ms/u/s!ArTqPk2pu-BkgSyMxvYV0Qrkg3VS.

     

    Best Regards!

    Dale