Sum of aggregated data

[amiskow]

Hi, I have a table with 4 products and the corresponding 4 quantities.

I need to group them (DAX command) as pictured.

Want to use it for a sales funnel

[amitchandak]

@amiskow , Dax is not the best way to do it . But please find the DAX

New Table =
Union(
summarize(Table, Table[Produkt_1], Table[Quantity_1]),
summarize(Table, Table[Produkt_2], Table[Quantity_2]),
summarize(Table, Table[Produkt_3], Table[Quantity_3]),
summarize(Table, Table[Produkt_4], Table[Quantity_4])
)

New Table =
var _tab = Union(
summarize(Table, Table[Produkt_1], Table[Quantity_1]),
summarize(Table, Table[Produkt_2], Table[Quantity_2]),
summarize(Table, Table[Produkt_3], Table[Quantity_3]),
summarize(Table, Table[Produkt_4], Table[Quantity_4])
)

return summarize(Table, Table[Produkt_1], "Quantity" , Summ(Table[Quantity_1]))

In the first statement, you can selectcolumns in place summarize.

Refer : https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

[wdx223_Daniel]

DAX is not good at processing data like that. M code is better to do this

[CNENFRNL]

@amiskow , Power Query is way much competent at such a data cleansing job,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXIGYicgdgFiIyA2gdKGSrE60WAZR6gKkEpjIDaHqjAFq3CG6nWBqgLpNwPrB7FBKlygZrhCaUOoOSAzLJRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [P1 = _t, P2 = _t, P3 = _t, P4 = _t, Q1 = _t, Q2 = _t, Q3 = _t, Q4 = _t]),
    Transformation =Table.FromColumns(List.Transform(List.Split(Table.ToColumns(Source),4), List.Combine)),
    #"Changed Type" = Table.TransformColumnTypes(Transformation,{{"Column2", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"Sum", each List.Sum([Column2])}})
in
    #"Grouped Rows"

[AlB · Accepted solution]

Hi @amiskow 

This can be best done in PQ. Place the following M code in a blank query to see the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXIGYicgdgFiIyA2gdKGSrE60WAZR6gKkEpjIDaHqjAFq3CG6nWBqgLpNwPrB7FBKlygZrhCaUOoOSAzLJRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [P1 = _t, P2 = _t, P3 = _t, P4 = _t, Q1 = _t, Q2 = _t, Q3 = _t, Q4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Q1", Int64.Type}, {"Q2", Int64.Type}, {"Q3", Int64.Type}, {"Q4", Int64.Type}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"P1", "P2", "P3", "P4"}),
    T1_ = Table.UnpivotOtherColumns(#"Removed Other Columns", {}, "Attribute", "Value"),
    auxT2_ = Table.SelectColumns(#"Changed Type",{"Q1", "Q2", "Q3", "Q4"}),
    T2_ = Table.UnpivotOtherColumns(auxT2_, {}, "Attribute", "Value"),
    #"Added Index" = Table.AddIndexColumn(T2_, "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each T1_[Value]{[Index]}, Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Attribute", "Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom", "Value"}),
    #"Grouped Rows" = Table.Group(#"Reordered Columns", {"Custom"}, {{"Count", each List.Sum([Value]), type number}})
in
    #"Grouped Rows"

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[amiskow]

Hi @AlB 

Thanks for help, but I'm not advanced in this way. I don't know how to do this 😞

[AlB]

@amiskow 

Go to the Query editor (Transform data) -> New Source -> Blank query

Select the query just created -> Advanced Editor

This will open a window with M code for the blank query:

let
    Source = ""
in
    Source

Now delete that code, paste the one I gave you before and clock on Done

On the right side (Applied steps) you'll see the step of the processing. Start by the first one (Source) move ahead one by one to see how it all works. You'll have to adapt it slightly to your data.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[amiskow]

Thank you very much, it works great : )