Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Anonymous
Not applicable

Get the group by value in a calculated column

Hello,

I got a simple task that I can't seem to get my head around.

 

I have a table with following data:

 

Order noPart NoComponent partQtyOnOrderDateDue
100012323410019.3.2020
100112323420020.3.2020
1001123567

200

20.3.2020

 

And I have another table with just the Part No. In that table I want to get the Qty per group, so in this case

123 = 100 + 200

 

I tried using SUMMARIZE, but I already have +30 tables in my dataset and don't want to clutter it any extra. 

 

The tables are related. And if there's a better way to get the qty of the distinct order no to the same product, please suggest so.

 

So order no 1001 will create 200 pcs 123, but use 234 and 567 to be created. And I need to be able to filter on the date (which is related to the date table).

 

Thanks in advance!

 

EDIT: because I need the date field, the best idea is probably to go with a measure that can calculate the average of qty on order based on the group function of order no. Is that possible?

3 REPLIES 3
amitchandak
Super User
Super User

@Anonymous , New column in  your part no table

New column = Sumx(filter(Table, Table[Part No] = part[Part no]),Table[QtyOnOrder])

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

Would work. But the column QtyOnOrder contains "duplicates" which is based on the order no, so the result with your expression would lead to 500 (instead of the wanted 300).

 

Thanks!

Anonymous
Not applicable

Found this:

 

Measure:

TotalOrderAmount = SUMX(DISTINCT(Table1[Order no]),CALCULATE(AVERAGE(Table1[qty])))

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.