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

[brownrobm]

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	B	26000
2	A	50000
3	A	15000

Type
Type	Description
A	Claim type A
B	Claim type B

My measure is defined as:

High Claimant Count = 

Calculate ([Distinct Member Count],

    FILTER (Values('Member'[ID]), Amount > 50000))

This works, until the Type Description is added to the query. Once that happens, Person 1 drops out of the result set because no one category exceeds 50k. What I need to do is sum up the values but still be able to show the sum total for the claim types for people who exceed 50k.

I feel like I am missing something very simple here.

Desired results
Type	Amount	Person Count
A	75000	2
B	26000	1

[Anonymous · Accepted solution]

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