Forum Discussion

pb1202's avatar
pb1202
Frequent Visitor
5 years ago
Solved

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.

  • Anonymous's avatar
    Anonymous
    5 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

  • Anonymous's avatar
    Anonymous
    Not 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] )
    • pb1202's avatar
      pb1202
      Frequent Visitor

      Hi Anonymous,

       

      Exactly. Now I got better understanding of SUMMARIZE v GROUPBY.

      Thank you soooooo much.