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:   Product Scenario Value  Region Bananas Actual 10 ...
  • v-yueyunzh-msft's avatar
    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