Rows grouped by Max of n-columns

[Anonymous]

Dear Community !

Need your help to created calculated column and get Max of parameters grouped by 'Clients', as shown in below tables.

Thanks in advance !

[v-yueyunzh-msft]

Hi , @Anonymous 

Thanks for your sample data !

For your need , you need to have the data structure like this:

If you want to create a table , you can use this dax:

Table 2 = ADDCOLUMNS( VALUES('Table'[Client]) ,
 "Max(Topic_1)",var _cur_client = [Client]  var _t = FILTER('Table','Table'[Client]=_cur_client && 'Table'[Topic]="Topic_1") 
 return MAXX(_t, [Value]),
 "Max(Topic_2)",var _cur_client = [Client]  var _t = FILTER('Table','Table'[Client]=_cur_client && 'Table'[Topic]="Topic_2") 
 return MAXX(_t, [Value]),
  "Max(Topic_3)",var _cur_client = [Client]  var _t = FILTER('Table','Table'[Client]=_cur_client && 'Table'[Topic]="Topic_3") 
 return MAXX(_t, [Value])
 )

The result is as follows:

You can also put the fields on the visual :

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

[Anonymous]

Thanks for your response @v-yueyunzh-msft , however, this is a monthly refresh activity and I don't have control on the input file/format. Is there a way you could help on the data which is received as shown please? Thanks in advance !

[v-yueyunzh-msft]

Hi , @Anonymous 

Do you mean your raw data structure is the 1nd picture you provided?Like this:

If this , when we load it to Power BI , it will look like this:

If this , we need to convert the table to the expected stucture we need , you can put this M code to "Advanced Edirtor" to refer to :

let
    Source = Excel.Workbook(File.Contents("C:\Users\v-yueyzhang\Desktop\mark\1226-1231.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Transposed Table" = Table.Transpose(Sheet1_Sheet),
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column1] <> "Summary")),
    #"Transposed Table1" = Table.Transpose(#"Filtered Rows"),
    #"Column Name" = Table.ToList(Table.CombineColumns(Table.Transpose( Table.FirstN(#"Transposed Table1",2)),{"Column1", "Column2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged")),
    Custom1 = Table.RenameColumns(Table.Skip(#"Transposed Table1",2),List.Zip({Table.ColumnNames(#"Transposed Table1"),#"Column Name"}) ),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Custom1, {"Client "}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Attribute.1", "Topic"}, {"Attribute.2", "Param"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Value", Int64.Type}, {"Client ", type text}})
in
    #"Changed Type1"

Then we can get the data table we need:

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

[amitchandak]

@Anonymous , if topic and param are row values

calculate(max([Value]), allexcept(Table, table[client], Table[topic]) )

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous · Accepted solution]

Hi Community, I have found the fix, but since I am new member, can't upload my work. Thanks for all those who responded. !

[Anonymous]

Thanks for your response @amitchandak but the data is sensitive and this form is not allowing me to attach .xlsx file. However, I have found a fix but unable to attach my work (.pbix)