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.
Hi,
Wish to calculate the following
Percentage of Shops which hit target which varies dynamically depending on w/edate and store name which selected in slicers
Data
Shop, Week Ending date, target met
target met is derived in table and can set correctly at table level to 0 depeidning on store percentage above 70, target met is set to 1
e.g
A, 05/01/2019,0
A,12/01/2019,0
A,19/01/2019,1
B, 05/01/2019,0
B,12/01/2019,1
B,19/01/2019,1
C, 05/01/2019,1
C,12/01/2019,1
C,19/01/2019,1
i.e. in above if looked at all this median OF A is 0, b is 1 and c is 1 i.e. 66% met target i.e 2 out of 3 stores met target.
However if were just looking at 19/01/2019 all met target i.e. would be 100%.
Tried
CALCULATE (
DISTINCTCOUNT ('Sales'[Store Number] ), FILTER('Sales',median('Sales'[target_met])=1))
but gives count of 3 rather than 2
How can we best achieve this in power bi?
Thanks
Solved! Go to Solution.
Hi @po
You may create a measure with SUMMARIZE Function.
Count = VAR _table = SUMMARIZE ( Sales, Sales[Store Number], "media", MEDIAN ( Sales[Target met] ) ) RETURN CALCULATE ( DISTINCTCOUNT ( Sales[Store Number] ), FILTER ( _table, [media] = 1 ) )
Regards,
Cherie
Hi @po
You may create a measure with SUMMARIZE Function.
Count = VAR _table = SUMMARIZE ( Sales, Sales[Store Number], "media", MEDIAN ( Sales[Target met] ) ) RETURN CALCULATE ( DISTINCTCOUNT ( Sales[Store Number] ), FILTER ( _table, [media] = 1 ) )
Regards,
Cherie
Thans for reply.
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 |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |