Forum Discussion

brownrobm's avatar
brownrobm
Frequent Visitor
8 years ago
Solved

Filter by aggregate value, and then summarize by a different column

I am attepting to create a measure that will identify people who have claims > $50k and then group those claims by another dimension.    Claims     Member Type Amount 1 A 25000 1 ...
  • Anonymous's avatar
    Anonymous
    8 years ago

    HI brownrobm,

     

    You can try to use below formula to create new table with specific summary conditions:

    Summary Table =
VAR list =
    CALCULATETABLE (
        VALUES ( 'Member'[ID] ),
        FILTER ( ALL ( 'Member' ), [Amount] > 50000 )
    )
RETURN
    SUMMARIZE (
        FILTER ( T1, [Member] IN list ),
        [Type],
        "Desc", LOOKUPVALUE ( T2[Description], T2[Type], [Type] ),
        "Amount", SUM ( T1[Amount] ),
        "Person", COUNTROWS ( VALUES ( T1[Member] ) )
    )

     

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
8 years ago

HI brownrobm,

 

You can try to use below formula to create new table with specific summary conditions:

Summary Table =
VAR list =
    CALCULATETABLE (
        VALUES ( 'Member'[ID] ),
        FILTER ( ALL ( 'Member' ), [Amount] > 50000 )
    )
RETURN
    SUMMARIZE (
        FILTER ( T1, [Member] IN list ),
        [Type],
        "Desc", LOOKUPVALUE ( T2[Description], T2[Type], [Type] ),
        "Amount", SUM ( T1[Amount] ),
        "Person", COUNTROWS ( VALUES ( T1[Member] ) )
    )

 

Regards,

Xiaoxin Sheng