Waterflow chart with measures

[ase]

Hi all, 

I am trying to make a Watefall chart which includes the differences between 2 measures. 

I have the following table:

Product	Scenario	Value	Region
Bananas	Actual	10	Asia
Apples	Actual	12	Asia
Lemons	Actual	14	Asia
Oranges	Actual	16	Asia
Bananas	Budget	11	Asia
Apples	Budget	14	Asia
Lemons	Budget	14	Asia
Oranges	Budget	12	Asia
Bananas	Actual	10	Europe
Apples	Actual	12	Europe
Lemons	Actual	14	Europe
Oranges	Actual	16	Europe
Bananas	Budget	11	Europe
Apples	Budget	14	Europe
Lemons	Budget	14	Europe
Oranges	Budget	12	Europe

And I have 2 measures, which show the Actuals and Budget for a Product group. 

So measure 1 shows Total Value Budget for Apples etc.

and measure 2 shows Total Value Actuals for Apples etc.

I have created a Selector for the Product (Apples etc.) so it serves as a filter betwen the Product types

The idea is to create a Wateflow chart, which has the Budgt on the left, all the deviations between Budget and Acutals and then Acutals on the Right. The value below should be the Regions. 

I saw online that I need to create an additional table, but this is where I get lost. 

Is anyone able to help with some guideance? 

Kind regards,

A

[v-yueyunzh-msft · Accepted solution]

Hi , @ase 

According to your description, you want to " Create a Wateflow chart, which has the Budgt on the left, all the deviations between Budget and Acutals and then Acutals on the Right. The value below should be the Regions and you have a [Product] slicer ".

Thanks for your sample data first! Here are the steps you can refer to :
(1)My test data is the same as yours.

(2)We can create  a blank query in Power Query Editor to create a dimension table .You can put this M code in "Adcanced Editor" to refer to :

let
    Source = {"Budget"} & List.Distinct( Table[Region]) & {"Actual"},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 1, 1, Int64.Type)
in
    #"Added Index"

And we need to create a relationship between two tables:

(3)Then we can create a measure like this:

Measure = var _row = MAX('Query1'[Column1])
var _actual_t =SUMX( FILTER( ALLSELECTED('Table') , 'Table'[Scenario]="Actual") , [Value])
var _budget_t =SUMX( FILTER( ALLSELECTED('Table') , 'Table'[Scenario]="Budget") , [Value])
var _actual =SUMX( FILTER( 'Table' , 'Table'[Scenario]="Actual") , [Value])
var _budget  =SUMX( FILTER( 'Table' , 'Table'[Scenario]="Budget") , [Value])

return
IF(_row = "Actual",_actual_t, IF( _row = "Budget" , _budget_t  ,_actual-_budget))

(4)For your need , we need to add a Custom visual called "Simple Waterfall" :

Then we can put the fields we need on the visual:

For your need , we need to close the "Show Cumlative Total" in the "Define Pillars" and then open the "Actual" as total:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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