Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
Solved! Go to 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
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
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
66 | |
34 | |
26 | |
22 |
User | Count |
---|---|
97 | |
96 | |
58 | |
45 | |
42 |