UNION and SUMMARISE across multiple Data Fields in single Table

[Jtbonner1986]

Hi All, 

hope i'm not being too dense here but i want to create a summarised fact table that can summarise values accross multiple data objects.

The example is i have 3 Tables PO Value, Commitment Value &  Turnover Value. I want to be able to structure the Fact table As 

Vendor Code | Year | Po Value | Commitment Value | Turnover

When i union and summarise i get multiple rows for the years: IE

Vendor         year        Po Value      Commitment Value       Turnover

Vendor 1      2021       100

Vendor 1      2021                                   100

Vendor 1                                                                                    100

when i want to see

Vendor         year        Po Value      Commitment Value       Turnover

Vendor 1      2021       100                       100                           100

the code i use is:

Fact = UNION(

    SUMMARIZECOLUMNS(

    'PO_Table '[Vendor],

    Calendar_Table[Year],

    "PO Value",SUM('PO_Table (11.1)'[PO Value]),

    "Commitment Value", BLANK(),

    "Turonover", BLANK()

    ),

    SUMMARIZECOLUMNS(

    PO_Commitment[Vendor],

    Calendar_Table[Year],

    "PO Value",BLANK(),

    "Commitment Value",SUM(PO_Commitment Value]),

    "Turnover",BLANK()

    SUMMARIZECOLUMNS(

    'Supplier Turnover'[Vendor],

    Calendar_Table[Year],

    "PO Value",BLANK(),

    "Commitment Value",BLANK(),

     "Turnover", SUM('Supplier Turnover'[Value])

))

    ),

 Any help is extremely useful

Josh

[v-easonf-msft]

Hi, @Jtbonner1986 

You can enter a table called "Suppliers" that contains all vendors.

Then you can try formula below to add the fact table.

New Fact =
ADDCOLUMNS (
    CROSSJOIN ( VALUES ( 'Vendor'[Vendor] ), VALUES ( Calendar_Table[Year] ) ),
    "PO Value",
        CALCULATE (
            SUM ( PO_Table[Po Value] ),
            FILTER (
                PO_Table,
                PO_Table[Vendor] = MAX ( Vendor[Vendor] )
                    && PO_Table[Year] = MAX ( Calendar_Table[Year] )
            )
        ),
    "Commitment Value",
        CALCULATE (
            SUM ( PO_Commitment[Commitment Value] ),
            FILTER (
                PO_Commitment,
                PO_Commitment[Vendor] = MAX ( Vendor[Vendor] )
                    && PO_Commitment[Year] = MAX ( Calendar_Table[Year] )
            )
        ),
    "Turnover Value",
        CALCULATE (
            SUM ( 'Supplier Turnover'[Turnover Value] ),
            FILTER (
                'Supplier Turnover',
                'Supplier Turnover'[Vendor] = MAX ( Vendor[Vendor] )
                    && 'Supplier Turnover'[Year] = MAX ( Calendar_Table[Year] )
            )
        )
)

Best Regards,
Community Support Team _ Eason

[Jtbonner1986]

Hello, 

Thanks so much for the reply. However there is no values in either PO Value, Commitment Value or Turnover Value. The table does create and has a single line per supplier & year tho... 

is this due to the table relationships? 

[v-easonf-msft]

Hi, @Jtbonner1986 

Perhaps your current model relationship conflicts with some of the filters in my dax formula. 

Please remove any additional relationships and see if the data in the calculated table is displayed correctly.

If it doesn't work, please upload a sample pbix to cloud storage and provide the sharing link here for further research.

Best Regards,
Community Support Team _ Eason