Summarize multiple fact tables

[123kerrod]

Hi,
I am working with two fact tables which are exactly the same format. Currently I have used DAX UNION
of the two tables into one table to use for all analysis/measures etc.

I now want to use SUMX function across this data however this method creates duplicate data. How do I create a table that essentially summarizes these two tables together where all rows are unique and values are added?

For background, I am not able to move all this into PowerQuery so am looking for a DAX suggestion.

Dummy fact table 1:
Prod Cust Amt
1 1 10
2 1 15
3 2 5
4 2 10
5 3 20

Dummy fact table 2:
Prod Cust Amt
1 1 5
2 2 10

Current UNION table:
Prod Cust Amt
1 1 10
2 1 15
3 2 5
4 2 10
5 3 20
1 1 5
2 2 10

Desired Table:
Prod Cust Amt
1 1 15
2 1 15
2 2 10
3 2 5
4 2 10
5 3 20

What would be the best combination to summarize these tables?

[Harsh]

Hello @123kerrod,

Thank You for posting your query onto the Forum.

Well rather than using the UNION function. You can directly do this transformation within the Power Query itself which will also optimize the model.

I’m providing a link below of a video from our education portal which will guide you about how you can append the same tables within the Power Query itself rather than using the DAX. And I also encourage you to go through the “Advanced Data Transformations & Modelling” course for which below is the link provided as well.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Appending queries

Data Transformations & Modeling

[AntrikshSharma]

@123kerrod Kerrod.pbix (21.8 KB)

Table = 
ADDCOLUMNS (
    SELECTCOLUMNS (
        DISTINCT (
            UNION (
                ALL ( FirstTable[Prod], FirstTable[Cust] ),
                ALL ( SecondTable[Prod], SecondTable[Cust] )
            )
        ),
        "Prod", FirstTable[Prod],
        "Cust", FirstTable[Cust]
    ),
    "Amt",
        VAR CurrentProd = [Prod]
        VAR CurrentCust = [Cust]
        VAR Result =
            CALCULATE (
                SUM ( FirstTable[Amt] ),
                FirstTable[Cust] = CurrentCust,
                FirstTable[Prod] = CurrentProd
            )
                + CALCULATE (
                    SUM ( SecondTable[Amt] ),
                    SecondTable[Cust] = CurrentCust,
                    SecondTable[Prod] = CurrentProd
                )
        RETURN
            Result
)

Faster yet little bit complicated to understand version:

Table 2 = 
ADDCOLUMNS (
    SELECTCOLUMNS (
        DISTINCT (
            UNION (
                ALL ( FirstTable[Prod], FirstTable[Cust] ),
                ALL ( SecondTable[Prod], SecondTable[Cust] )
            )
        ),
        "Prod", FirstTable[Prod],
        "Cust", FirstTable[Cust]
    ),
    "Amt",
        VAR CurrentProd = [Prod]
        VAR CurrentCust = [Cust]
        VAR Result =
            CALCULATE (
                SUM ( FirstTable[Amt] ),
                TREATAS ( { CurrentCust }, FirstTable[Cust] ),
                TREATAS ( { CurrentProd }, FirstTable[Prod] )
            )
                + CALCULATE (
                    SUM ( SecondTable[Amt] ),
                    TREATAS ( { CurrentCust }, SecondTable[Cust] ),
                    TREATAS ( { CurrentProd }, SecondTable[Prod] )
                )
        RETURN
            Result
)

[James_Thomas]

Hi @123kerrod,

Thank you for posting your query in EDNA Forum.

We can solve this through Power Query transformation using Append & Group by Functions.

First Table:

Second Table:

Appended Table: With Duplicates

Group By:
Use Advance Group by function to combine both “Prod & Cust” columns and use Sum function to aggregate “Amt” column in new column.

Final Table:

Hope this solves your problem.

Regards
James

[123kerrod]

Hi,
Thank you for the prompt replies. I agree that PowerQuery is the best for this transformation however in this instance I need it to be in DAX.

Solution by @AntrikshSharma solves it elegantly.

Thank you!