Forum Discussion
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
- amitchandakSuper User
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])
- Md_asgarFrequent Visitor
amitchandak sure, below is the sample data link :