Forum Discussion
Summarize a summarized table, as a measure
Hi all,
Got one question on creating a power BI measure that achieves the following function.
Problem set is like this: powerbi - Average of Grouped Sum - Stack Overflow
A table Transactions with 3 columns (added 3 more rows):
CustomerID | Total Transaction Amount | Payment Type -----------|--------------------------|------------- 1 | 10.00 | Card 1 | 5.00 | Cash 2 | 5.00 | Cash
2 | 10 | Card
2 | 8 | Cash
1 | 8 | Card
But different from the function sought in the above link, I want to 1) calculate ”Sum by CustomerID & Payment Type”, which is the sum of Total Transaction Amount for each pair of CustomerID and Payment Type, and 2) get the median of “Sum by CustomerID & Payment Type” for each Payment Type.
For the above example, the output would be:
Payment Type | Median Total Spent | -------------|--------------------------| Card | Median of (10, 10 + 8 ) |
Cash | Median of (5, 5 + 8 ) |
Step 1) can be done by creating a table:
SumTab = SUMMARIZE(Transactions,
Transactions[CustomerID], Transactions[Payment Type],
"Total Spent", SUM(Transactions[Total Transaction Amount]))
To do step 2), some sort of re-summarizing the above table:
SUMMARIZE(SumTab,
SumTab[Payment Type],
“Median Total Spent”, MEDIAN(SumTab[‘Total Spent’])
The question is the two summarizing functions cannot be combined as one measure after I tried a few times. The SumTab as a measure cannot be used to get its column “Total Spent”.
Similar to the reason in the above link, I want to do this so that I can keep the filters applied somewhere on the page, instead of creating a new table which is isolated from the those filters.
Any idea would be appreciated.
- Anonymous5 years ago
Hi pb1202
Is it what you want?
test = VAR T1 = GROUPBY ( Transactions, Transactions[CustomerID], Transactions[Payment Type], "Total Spent", SUMX ( CURRENTGROUP (), Transactions[Total Transaction Amount] ) ) RETURN MEDIANX ( T1, [Total Spent] )
2 Replies
- AnonymousNot applicable
Hi pb1202
Is it what you want?
test = VAR T1 = GROUPBY ( Transactions, Transactions[CustomerID], Transactions[Payment Type], "Total Spent", SUMX ( CURRENTGROUP (), Transactions[Total Transaction Amount] ) ) RETURN MEDIANX ( T1, [Total Spent] )- pb1202Frequent Visitor
Hi Anonymous,
Exactly. Now I got better understanding of SUMMARIZE v GROUPBY.
Thank you soooooo much.