Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Need help for matrix view

Hi Community members,

I need your help to buiId a matrix view shown as: for goal g1

yearjanfebmaraprmayjunjulyaugsepoctnovdecYTDtarget
2020 13  5 6 9 11 14      1416
2019 12 4 5 6 8 10 11 12 13 13 15 15 15

I tried but it shows target for each month which is undesirable.

here the YTD is the actual_score for latest month or month=-2  and

target is the value from target column where month=-2 or it can be the average of target for all months.

Please help me out to solve this.

Thanks,

Arundhati

 

My demo data is  as

yearmonthidgoalscoretaget
2020-2g11416
20201g1116
20202g1316
20203g1516
20204g1616
20205g1916
20206g11116
20207g11416
2019-2g11515
20191g1115
20192g1215
20193g1415
20194g1515
20195g1615
20196g1815
20197g11015
20198g11115
20199g11215
201910g11315
201911g11315
201912g11515
2020-2g210.211
20201g21.111
20202g22.311
20203g24.511
20204g2511
20205g26.111
20206g28.111
20207g210.211
2019-2g21011
20191g21.311
20192g22.311
20193g23.511
20194g25.711
20195g2611
20196g26.411
20197g27.311
20198g28.111
20199g28.411
201910g2911
201911g29.111
201912g21011
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

    You can make transformations in Power Query Editor:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdJLFoMgDAXQvTC2HBL+a/E47v53UIUSxAdOMugVyEuz74oNG7WpD5/lS2chd5Wgjk2QxIDkmAWyjTyQaxSAfKMMFKQN7CPO26c8ZvO13HDMNpAcYyDJ5oAkG74l2QKQZEtAPZoBS+NIBsti2H+5qqJFpDdcDPO2ReU5o7le9dyj8rMmQDnK2gLahk57QNcQyTcKkxdDwzTBuEpyWymWP4VgpWpMC7iK2dfqQjvG7ItVYuoI2IMCScygHaDEjJN20mJAfb0q4rV1v/j/2cNkPnlyLeFkjx8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [year = _t, monthid = _t, goal = _t, score = _t, taget = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"year", Int64.Type}, {"monthid", Int64.Type}, {"goal", type text}, {"score", type number}, {"taget", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([goal] = "g1")),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Filtered Rows", {{"monthid", type text}}, "zh-CN"), List.Distinct(Table.TransformColumnTypes(#"Filtered Rows", {{"monthid", type text}}, "zh-CN")[monthid]), "monthid", "score", List.Sum),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"-2", "YTD"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"goal"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"year", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "YTD", "taget"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"year", Order.Descending}})
in
    #"Sorted Rows"

    Best Regards

    Rena

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Amit,

      Can you please share .pbix file to learn more .

  • andre's avatar
    andre
    Memorable Member
    6 years ago

    you need to create a table with these values in a single column:

    janfebmaraprmayjunjulyaugsepoctnovdecYTDtarget

     

    that colum  of that table will go into the columns for your matrix.  then you need to write a new smart calc that woudl look at the value of the column and then calculate the proper value for the cell.  so you would have a switch() statement with 14 different conditions to check.

     

    or you could do it with a calculation group, you would not need a swith statement

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous 
     You can use this measure over values in your matrix visual.

     

    Target = 
SWITCH(
    TRUE()
    , ISINSCOPE('Table'[monthid]), SUM('Table'[score])
    , MAX('Table'[taget])
)

    Then change Column Subtotal Label to Target.

     

    Appreciate with Kudos.

    Please mark as solution if this resolves your problem.

     

    Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

    You can make transformations in Power Query Editor:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdJLFoMgDAXQvTC2HBL+a/E47v53UIUSxAdOMugVyEuz74oNG7WpD5/lS2chd5Wgjk2QxIDkmAWyjTyQaxSAfKMMFKQN7CPO26c8ZvO13HDMNpAcYyDJ5oAkG74l2QKQZEtAPZoBS+NIBsti2H+5qqJFpDdcDPO2ReU5o7le9dyj8rMmQDnK2gLahk57QNcQyTcKkxdDwzTBuEpyWymWP4VgpWpMC7iK2dfqQjvG7ItVYuoI2IMCScygHaDEjJN20mJAfb0q4rV1v/j/2cNkPnlyLeFkjx8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [year = _t, monthid = _t, goal = _t, score = _t, taget = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"year", Int64.Type}, {"monthid", Int64.Type}, {"goal", type text}, {"score", type number}, {"taget", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([goal] = "g1")),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Filtered Rows", {{"monthid", type text}}, "zh-CN"), List.Distinct(Table.TransformColumnTypes(#"Filtered Rows", {{"monthid", type text}}, "zh-CN")[monthid]), "monthid", "score", List.Sum),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"-2", "YTD"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"goal"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"year", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "YTD", "taget"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"year", Order.Descending}})
in
    #"Sorted Rows"

    Best Regards

    Rena