cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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 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
1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.