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.
Ok I'm stumped, I feel like I should know this and it should be simple but for some reason I can't get this to work. I have two tables, one has a list of clients and each has an assigned level. The other table has the level number and the value of that level. Example:
Table One:
Customer A - level 1
Customer B - Level 2
Customer C - Level 3
Customer D - Level 4
Table Two:
Level 1 = 1
Level 2 = 1
Level 3 = 3
Level 4 = 5
The two tables are linked through the level number (Levels 1-4)
I have a measure that counts the number of Customers in each level:
No. of Clients = COUNT('Client Contracts (CC1936)'[Program Level])
This creates the following visual for me, (the rows are filter by the name of the clients' advisor, not included in the visual for privacy)
Now all i want to do is create a measure that calculates the total value of the clients across the rows. So in excel it would just be the number of clients per level * the value of the level. For the first row of the above visual this would be Level 2 (3*1)=3, level 3 (3*3)=9, Level 4 (1*5)=5.
I've tried several different measures but I'm not getting it right. Any help would be appreciated, I'm just going around in circles. Thanks,
Solved! Go to Solution.
Hi @eburke,
There are two approaches. The measure could be the one below.
Measure = COUNT('Client Contracts (CC1936)'[Program Level]) * min(Table2[Value])
1. Use the Levels from table Client Contracts (CC1936) and change the Cross filter direction to Both.
2. Use the Levels from table Table2.
You can try it out in this file.
Best Regards,
Dale
Hi @eburke,
Try this measure out please.
Measure 2 = VAR summaried = SUMMARIZE ( 'Table1', 'Table1'[Advisor], 'Table2'[Level], 'Table2'[Value], "sumValue", COUNT ( Table1[Customer] ) * 'table2'[value] ) RETURN IF ( HASONEFILTER ( Table1[Advisor] ) && HASONEFILTER ( Table2[Level] ), COUNT ( Table1[Customer] ) * MIN ( Table2[Value] ), SUMX ( summaried, [sumValue] ) )
Another simple way is bringing the value to the table 1 from table 2.
Valuefromtable2 = RELATED(Table2[Value] )
Best Regards,
Dale
Hi @eburke,
There are two approaches. The measure could be the one below.
Measure = COUNT('Client Contracts (CC1936)'[Program Level]) * min(Table2[Value])
1. Use the Levels from table Client Contracts (CC1936) and change the Cross filter direction to Both.
2. Use the Levels from table Table2.
You can try it out in this file.
Best Regards,
Dale
Sorry just noticed that this gives the correct value in the table but the total is only calculated using the minimum level value as well. I need the total to be the sum of all the values. is there anyway i can get this to work?
Hi @eburke,
Try this measure out please.
Measure 2 = VAR summaried = SUMMARIZE ( 'Table1', 'Table1'[Advisor], 'Table2'[Level], 'Table2'[Value], "sumValue", COUNT ( Table1[Customer] ) * 'table2'[value] ) RETURN IF ( HASONEFILTER ( Table1[Advisor] ) && HASONEFILTER ( Table2[Level] ), COUNT ( Table1[Customer] ) * MIN ( Table2[Value] ), SUMX ( summaried, [sumValue] ) )
Another simple way is bringing the value to the table 1 from table 2.
Valuefromtable2 = RELATED(Table2[Value] )
Best Regards,
Dale
Thank you that's worked perfectly. Much appreciated.
Awesome that worked, thanks so much.
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |