Manipulating a waterfall chart with categorical data on the x-axis

[Anonymous]

Hi

I have a challenge regarding a waterfall chart. 

The visual shows four categories that are summed within four subsums, to make a build down waterfall:

The data is created in a calculated table and looks like this:

ValueCategorySubsumOrder

10	Cat1	Subsum1	1
10	Cat1	Subsum2	1
-5	Cat2	Subsum2	2
10	Cat1	Subsum3	1
-5	Cat2	Subsum3	2
-1	Cat3	Subsum3	3
10	Cat1	Subsum4	1
-5	Cat2	Subsum4	2
-1	Cat3	Subsum4	3
-1	Cat4	Subsum4	4

I have three questions:

1) Is there a smarter way of making the calculated table to make the visual build down correctly? E.g. so that values of Cat1 doesn't have to be in Subsum2, Subsum3 and Subsum4 because Cat1 is already included in Subsum1?

2) How can I hide the empty values in the visual? If Cat2 is not showed in Subsum1, it should not be visible in the waterfall.

3) How do I make the visual sort correctly, so that the order on the x-axis is Subsum1 -> Cat1 -> Cat2 -> Cat3 -> Cat4 -> Subsum2 -> Cat1 -> etc.?

Thank you very much

Best regards

Mattias

[Icey · Accepted solution]

Hi @Anonymous ,

Based on my test, the default waterfall visual which Power BI Desktop provided can't achieve your requirements.

Please try to use custom visual: Waterfall Chart - xViz.

Create a measure like below and then put it into "Filters on this visual". Then, you'll get what you want.

Measure = 
VAR Count_ =
    CALCULATE (
        COUNT ( 'Table'[Category] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Category] = MAX ( 'Table'[Category] )
                && 'Table'[Subsum] <= MAX ( 'Table'[Subsum] )
        )
    )
RETURN
    IF ( Count_ = 1, 1 )

BTW, .pbix file attached.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.