Forum Discussion

ase's avatar
ase
Frequent Visitor
3 years ago
Solved

Waterflow chart with measures

Hi all, 

 

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

 

I have the following table:

 

ProductScenarioValue Region
BananasActual10Asia
ApplesActual12Asia
LemonsActual14Asia
OrangesActual16Asia
BananasBudget 11Asia
ApplesBudget 14Asia
LemonsBudget 14Asia
OrangesBudget 12Asia
BananasActual10Europe
ApplesActual12Europe
LemonsActual14Europe
OrangesActual16Europe
BananasBudget 11Europe
ApplesBudget 14Europe
LemonsBudget 14Europe
OrangesBudget 12Europe

 

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's avatar
    v-yueyunzh-msft
    3 years ago

    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

     

1 Reply

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Community Support
    3 years ago

    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