Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |