Total of all transactions related to one item type

[jaliu1290]

Hello Power BI Community!!

I have a table of Items, Amounts, and associated Transaction IDs and would like a measure to calculate the Expected Outcome.

Item	Amount	Transaction	Expected Outcome (Total of All Transactions)
Square	5	ABC	60
Circle	10	ABC	35
Triangle	20	ABC	80
Square	5	DEF	60
Triangle	20	DEF	80
Triangle	20	HIJ	80

Transaction total for ABC = 35, DEF = 25, and HIJ = 20.

Therefore, if want to be able to slice by Square, then I want it to retrieve Transactions ABC and DEF that contained Square items, but then finally sum the entire total amount of those Transactions.

Thanks in advance,

Jamie

[Kedar_Pande]

@jaliu1290 

Measure:

Expected Outcome = 
CALCULATE(
SUM('Table'[Amount]),
FILTER(
ALL('Table'[Transaction]),
CONTAINS(
FILTER('Table', 'Table'[Item] = SELECTEDVALUE('Table'[Item])),
'Table'[Transaction],
'Table'[Transaction]
)
)
)

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[v-linhuizh-msft]

Thanks for the replies from ThxAlot and rohit1991.

Hi @jaliu1290 ,

According to your description I tried different things in Power Query, here's how I did it:

1.Duplicate the table:

2. Use these two tables to create a new merged table:

3. Expand the columns:

4. Perform group by operations:

5. Create a many-to-many relationship between the original table and the new table, and create the following visual:

Result:

Best Regards,
Zhu

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[rohit1991]

To achieve this in Power BI, you can use a combination of DAX measures and filtering logic. Here's a step-by-step guide:

Steps:

1. Create a Relationship (if needed): Ensure your data model has a proper relationship between your Items table and Transactions table if they are in separate tables.

2. Create a DAX Measure for Expected Outcome: Use the following DAX formula to create a measure that calculates the Expected Outcome:
   Expected Outcome = VAR SelectedItems = VALUES(Items[Item])VAR TransactionsWithSelectedItems = CALCULATETABLE(VALUES(Transactions[Transaction]),FILTER(ALL(Items),Items[Item] IN SelectedItems))
   RETURNCALCULATE(SUM(Transactions[Amount]),Transactions[Transaction] IN TransactionsWithSelectedItems)

3. Visualize the Data:

   • Add a slicer with the Item field.
   • Add a table visual showing the Item, Transaction, and the newly created Expected Outcome measure.
4. Testing:Select different items in the slicer (e.g., "Square"), and the measure should display the summed total of all relevant transactions.

[ThxAlot · Accepted solution]

[jaliu1290]

This was the simplest and most elegant solution. Thank you!!!