Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
User | Count |
---|---|
77 | |
77 | |
68 | |
67 | |
49 |
User | Count |
---|---|
108 | |
105 | |
93 | |
83 | |
64 |