Forum Discussion

Md_asgar's avatar
Md_asgar
Frequent Visitor
2 years ago

How to do 3 layer group by

How to do the below in Powerbi:

First you group by three columns ID, desc and qty. second, from the output above, you group by ID and Desc, lastly by ID.


I have a table with the below columns:
ProdoNum: number of each product
ProdoCls: Product column number
ProdoDesc: Product description
Location: Location of the product delivery
ProdStat: Product state
UnitCost: Unit cost of each product
SalePrce: Selling price of the product
ReviewDT: Date on which product is reviewed
QuantityOH: quantity of the product send to each seller
InvValue: final bill send with the product

 

The below is the M-Code I have written, but in the final output I am not getting the more then 1,

can anyone please help me on this:
let
// Load your table here
Source = #"p_InventoryAllRegions",

// First group by ProdoNum, ProdoDesc, and QuantityOH
GroupedStep1 = Table.Group(
Source,
{"ProdoNum", "ProdoDesc", "QuantityOH"},
{{"Count of Records", each Table.RowCount(_), Int64.Type}}
),

// Second group by ProdoNum and ProdoDesc, aggregating the counts
GroupedStep2 = Table.Group(
GroupedStep1,
{"ProdoNum", "ProdoDesc"},
{{"Total QuantityOH Groups", each List.Sum([Count of Records]), Int64.Type}}
),

// Third group by ProdoNum, aggregating the counts again
GroupedStep3 = Table.Group(
GroupedStep2,
{"ProdoNum"},
{{"Total Product Groups", each List.Sum([Total QuantityOH Groups]), Int64.Type}}
)
in
GroupedStep3

2 Replies

  • Md_asgar , The code seem fine.  Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

     

    You can do this using a measure in visual

    DAX Measure =

    Sumx(Summarize(Table, Table[ProdoNum], Table[ProdoDesc], Table[QuantityOH], "_1", countrows(Table)),[_1])