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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
adeel726
Frequent Visitor

DISTINCT UNION & Summarized Combined.

Hi 

 

I have two tables and i am trying to make a summary table using "New Table". The result of the two tables should be something similar to "group by" function bases on both tables. the uniqunes is based on Material & Period combination. Previously i have used Summary = DISTINCT(UNION(VALUES('Table2'[Material]),VALUES(Table1[Material]))) function for another project however that is only bringing unique materials across both tables i need it to be unique based on Material & Period and also bring in the Period field. Then by using SUMMARIZE functionality i want to bring the totals across. 

 

i have ways to achieve this by having a few tables and using merge/append etc. but i want one formula that can achieve this. 

 


Table 1 (Data)

MaterialPeriodProduction OrderPlanned Order
PART101/09/20191188
PART101/10/2019 96
PART201/09/20191132

 

Table 2 (Data

MaterialPeriodDelivered Quantity
PART101/09/201920
PART101/09/201920
PART301/10/201920

 

 

Expected Result for the new table.

 
Material

(Table 1 & 2)

Period

(Table 1)

Prod Order

(Table 1)

Planned Orders

(Table 1)

Delivery Quantity (Table2)

Part101/09/2019118840
Part101/10/20190960
Part201/09/20191132 
Part301/10/2019  10
1 ACCEPTED SOLUTION

Hi @adeel726 ,

 

we can create a calculated table to meet your requirement:

 

NewTable = 
ADDCOLUMNS (
    DISTINCT (
        UNION (
            SELECTCOLUMNS ( 'Table1', "Material", [Material], "Period", [Period] ),
            SELECTCOLUMNS ( 'Table2', "Material", [Material], "Period", [Period] )
        )
    ),
    "Prod Order",
    VAR M = [Material]
    VAR P = [Period]
    RETURN
        CALCULATE (
            SUM ( 'Table1'[Production Order] ),
            FILTER ( 'Table1', 'Table1'[Material] = M && 'Table1'[Period] = P )
        ),
    "Planned Order",
    VAR M = [Material]
    VAR P = [Period]
    RETURN
        CALCULATE (
            SUM ( 'Table1'[Planned Order] ),
            FILTER ( 'Table1', 'Table1'[Material] = M && 'Table1'[Period] = P )
        ),
    "Delivery Quantity",
    VAR M = [Material]
    VAR P = [Period]
    RETURN
        CALCULATE (
            SUM ( 'Table2'[Delivered Quantity] ),
            FILTER ( 'Table2', 'Table2'[Material] = M && 'Table2'[Period] = P )
        )
)

 

15.jpg

 


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

Create below calculated table.

 

Table =
Var First=SUMMARIZE(Sheet4,Sheet4[Material],Sheet4[Period],"Plan order",SUM(Sheet4[Planned Order]),"Prod order",SUM(Sheet4[Production Order]),"Delivered Quantity",0)
Var Second_tab=SUMMARIZE(Sheet5,Sheet5[Material],Sheet5[Period],"Plan order",0,"Prod order",0,"Delivered Quantity",SUM(Sheet5[Delivered Quantity]))
return
UNION(First,Second_tab)
 
 
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

Hi Pravin

 

Close, the only remaining issue is that from the second table it is creating a new line as opposed to adding the delivery quantity to the same line. As you can see row 5 has a delivery quantity of 30 but that should be in row 1 as the material and preiod are the same. Row 6 is fine as the material is different and there are no records prior that match the material/period combination.

 

FYI the formula i uses is below. 

Table =
Var Tab1=SUMMARIZE(Table1,[Material],[Period],"Plan order",SUM(Table1[Planned Orders]),"Prod order",SUM(Table1[Prod Order]),"Delivered Quantity",0)
Var Tab2=SUMMARIZE('Table2',[Material],[Period],"Plan order",0,"Prod order",0,"Delivered Quantity",SUM('Table2'[Open Quantity])) return
UNION(Tab1,Tab2)

 

MaterialPeriodProd OrderPlanned OrdersDelivery Quantity 
Part101/09/201911880
Part201/09/201911320
Part101/10/20190960
Part201/10/2019000
Part101/09/20190030
Part301/10/20190020

Hi @adeel726 ,

 

we can create a calculated table to meet your requirement:

 

NewTable = 
ADDCOLUMNS (
    DISTINCT (
        UNION (
            SELECTCOLUMNS ( 'Table1', "Material", [Material], "Period", [Period] ),
            SELECTCOLUMNS ( 'Table2', "Material", [Material], "Period", [Period] )
        )
    ),
    "Prod Order",
    VAR M = [Material]
    VAR P = [Period]
    RETURN
        CALCULATE (
            SUM ( 'Table1'[Production Order] ),
            FILTER ( 'Table1', 'Table1'[Material] = M && 'Table1'[Period] = P )
        ),
    "Planned Order",
    VAR M = [Material]
    VAR P = [Period]
    RETURN
        CALCULATE (
            SUM ( 'Table1'[Planned Order] ),
            FILTER ( 'Table1', 'Table1'[Material] = M && 'Table1'[Period] = P )
        ),
    "Delivery Quantity",
    VAR M = [Material]
    VAR P = [Period]
    RETURN
        CALCULATE (
            SUM ( 'Table2'[Delivered Quantity] ),
            FILTER ( 'Table2', 'Table2'[Material] = M && 'Table2'[Period] = P )
        )
)

 

15.jpg

 


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks that worked

parry2k
Super User
Super User

@adeel726 instead of summarizing the tables, the best practice would be to add part no and date table in your model and set relationship of these tables with your these transactio tables and from there you can easily visualize the data the way you want.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

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! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.