How to display one transaction amount per record ID?

[BIuser45]

I have a query that have a list of transaction in the fact table called Transaction in Power BI, which is simliar to the excel chart shown below. The same transaction might include different purchased beverages and would be dedicated for different events. The event is presented by an event id and that id is linked to a dimension table called Event. 

In transction ID 1, several alcohol were bought, but some are used for event 32 and the other were used for event 5. Because there are two event ID in the same transaction. Two of the same record of transaction amout and beverage type appears in the table. 

Transaction ID 2 should only have one transaction record, but because in the same transaction there were two types of beverages brought, so the record is seperated into two to show that juice and milk were purchased in the same transaction. This causes a problem when the total amount for all transaction is calculated. The total amount should be $125 instead of $240 that is shown in the chart. 

I only want to show one transaction amount per transaction ID, so that when I calculate the sum of transactions in the chart it should be $125 instead of $240. How would I do that? 

Transaction Table

Event Table

[rubayatyasmin]

Hi, @BIuser45 

I was able to get the result using, summarize function. 

used DAX: 

Total Unique TransId Amount =

SUMX(

    SUMMARIZE(

        Table1,

        Table1[TransId],

        "AmountPerTransId",

        MAX(Table1[amount])

    ),

    [AmountPerTransId]

)

[BIuser45]

What is  "AmountPerTransId" and [AmountPerTransId]? I can find that variable in your table.

[rubayatyasmin]

Hi, @BIuser45 

I am creating AmountPerTransId column here using the Summarize. and then using it as the 2nd argument for SUMX. Which then ultimately results 125. You can name it anything you want. If you name is MaxTransID, you will have to use [MaxTransID] as the 2nd argument for the SUMX func. 

here is the documentation for better understanding. 

SUMMARIZE function (DAX) - DAX | Microsoft Learn