Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Baseline Matrix based on monthly average vs yearly average

Hello,


I'm extremely new in Power BI and Dax(first time using it, no training) and I was recently tasked with creating matrix that compares the average sales volumes of products(represented by keys) with a baseline.

The baseline is created through a simple IF function in excel, which compares the yearly average with the monthly one.

 

I managed to create a matrix with the average of the sales volume, but I can't find a way to recreate the baseline part(can't wrap my head around how I take the total average that the matrix creates and create new columns in which the baseline number is shown based on the IF function), either in the same matrix or in a separate matrix that I'll put side by side.

 

Thank you! ^_^

Need Help
Show and Tell
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    Hi Anonymous ,

     

    Based on your description, I have created a simple sample:

    Please try:

    First, Unpivot the columns:

    Here is the M code:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rY6xEcAwCAN3oU5hwAZTJhnD5/3XCCgZIY3QIV7HWnTSQSKR2qWX+kg191ItPxv8u38vJ6eqSbFIOSb2SvtYdKUPtLFGYTyQS04gozJRRw1bq9GZccJfqxVngXY1AE0c/fffP+8H", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, #"1.2022" = _t, #"2.2022" = _t, #"3.2022" = _t, #"4.2022" = _t, #"5.2022" = _t, #"6.2022" = _t, #"7.2022" = _t, #"8.2022" = _t, #"9.2022" = _t, #"10.2022" = _t, #"11.2022" = _t, #"12.2022" = _t, #"Last 12Month" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Key", "Last 12Month"}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Last 12Month", Int64.Type}, {"Value", Int64.Type}, {"Attribute", type date}})
in
    #"Changed Type"

    Then create a new measure:

    BaseLine = IF(MAX('Table'[Value])<=MAX('Table'[Last 12Month]),MAX('Table'[Value]),MAX('Table'[Last 12Month]))

    Create a matrix visual:

    Final output:

     

    Best Regards,

    Jianbo Li

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

1 Reply

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

    Hi Anonymous ,

     

    Based on your description, I have created a simple sample:

    Please try:

    First, Unpivot the columns:

    Here is the M code:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rY6xEcAwCAN3oU5hwAZTJhnD5/3XCCgZIY3QIV7HWnTSQSKR2qWX+kg191ItPxv8u38vJ6eqSbFIOSb2SvtYdKUPtLFGYTyQS04gozJRRw1bq9GZccJfqxVngXY1AE0c/fffP+8H", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, #"1.2022" = _t, #"2.2022" = _t, #"3.2022" = _t, #"4.2022" = _t, #"5.2022" = _t, #"6.2022" = _t, #"7.2022" = _t, #"8.2022" = _t, #"9.2022" = _t, #"10.2022" = _t, #"11.2022" = _t, #"12.2022" = _t, #"Last 12Month" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Key", "Last 12Month"}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Last 12Month", Int64.Type}, {"Value", Int64.Type}, {"Attribute", type date}})
in
    #"Changed Type"

    Then create a new measure:

    BaseLine = IF(MAX('Table'[Value])<=MAX('Table'[Last 12Month]),MAX('Table'[Value]),MAX('Table'[Last 12Month]))

    Create a matrix visual:

    Final output:

     

    Best Regards,

    Jianbo Li

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