Waterfall chart: how are totals calculated?

[Anne25]

Hi all,

I am trying to make a waterfall chart to show the impact of price, quantity, new and discontinued products on revenue. 

I have found a very good start on https://businessintelligist.com/2017/04/03/price-volume-mix-analysis-using-power-bi/

Below you see the outcome of following this method. The Y-axis in the waterfall chart is a switch formula for the different blocks in this waterfall chart (price, volume, mix, new products, discontinued products).

However, I would like to show this evolution from prior year to current year. By adding PY in the switch formula (and in the column to which the switch is referring), I already obtained the following:

The numbers are exactly how I want it, but I would like the PY block in the total color and text "CY" instead of "Total" on the x-axis. Would anyone know how to do this? Or can someone tell me how the totals are calculated in a waterfall so I know how I can write a measure for the totals that I can combine with the measure for the different blocks (volume, new products, mix, price and discontinued products)? 

Many thanks in advance!

Anne

[Anne25]

Hi,

As no one seems to know the answer, can I assume that it is not possible in PBI to show previous year and current year as total in my case? And that no one exactly knows how the totals are calculated in a waterfall chart?

Kind regards,

Anne

[Anne25]

Hi all,

Does anyone know how I can make PY and CY in the color of "Total"? Or can someone explain me how totals are calculated in a waterfall chart?

Many thanks in advance.

Anne

[Greg_Deckler]

Two options as I see it. Create a text visual and place it over the Total. Hacky but quick.

Otherwise, well, honestly I don't know, I'd have to see your data to understand how to construct that measure. Is that in the link?

[Anne25]

Hi @Greg_Deckler ,

Yes, in that link there is a link to download the PBI file.
Only thing I added to this file is an additional table PVM_trial1 for reference in the switch.

And I have tweeked the switch in the original PVM measure:

PVMix_trial1 =

VAR PriceImpact =

SUMX(

ADDCOLUMNS(

VALUES('Product'[Product]),

"Price", ([Volume PY])

* (

if(

ISBLANK([Price PY]) || ISBLANK([Price CY]) ,

0 ,

[Price CY] - [Price PY]

)

)

) ,

[Price]

)

var VolumeImpact =

SUMX(

ADDCOLUMNS(

VALUES('Product'[Product]) ,

"Volume" , ([Price PY])

* (

if(

ISBLANK([Price PY]) || ISBLANK([Price CY]) ,

0 ,

[Volume CY] - [Volume PY]

)

)

) ,

[Volume]

)

var MixImpact =

SUMX(

ADDCOLUMNS(

VALUES('Product'[Product]),

"Mix",

if(

ISBLANK([Price PY]) || ISBLANK([Price CY]) ,

0 ,

([Price CY] - [Price PY])

)

* ([Volume CY] - [Volume PY])

) ,

[Mix]

)

Var NewProductImpact =

sumx(

ADDCOLUMNS(

VALUES('Product'[Product]) ,

"New" ,

if(

ISBLANK(Sales[Sales PY]) || Sales[Sales PY] = 0 ,

Sales[Sales CY] ,

0

)

) ,

[New]

)

var DiscontinuedProductImpact = Sales[Sales YoY]

- VolumeImpact

- NewProductImpact

- PriceImpact

- MixImpact

return

SWITCH(

max('PVM_trial1'[Index]),

1 , Sales[Sales PY] ,

2 , PriceImpact,

3 , VolumeImpact ,

4 , MixImpact ,

5 , NewProductImpact,

6 , DiscontinuedProductImpact ,

BLANK()

)

Then my outcome is this, but I am now trying to understand how the totals are calculated so I can make show PY and CY as a total.

[amitchandak]

@Anne25 , refer if this can help

https://community.powerbi.com/t5/Desktop/Change-the-total-label-in-waterfall-chart/m-p/598484

[Anne25]

@amitchandak , thank you for your help. 

The solution in your link suggests to paste a text box "CY" over the text "Total".

However, then I am still looking for a way to change the color of the PY block to the blue color of total.