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 units), is also divided in 3 sub-categories.

I have used two different visuals (waterfall & stacked bars) but I haven’t achieved the expected result.

The problem of using the waterfall chart is that when I arrive at the position of the category 1-6, the next values are represented starting at the top of the bar:

 

 

The workaround that I have tried with the stacked columns is to us two bars, one with the correct value and one with a value that will be painted white. So for example, the category 1-1 will have two values, 40, which will be on the top, and 460 (500-40) which will be painted blank at the bottom.

The problem of this workaround is that all the top bars should have the same color, so I can’t change the color when I arrive at the category 1-6, which is something essential.

 

Does anyone know how I can achieve what I am looking for?

Thank you

Tips and Tricks

3 Replies

  • 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