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 everyone,
Theres kind of complicated scenario. This is my test dataset.
Target : Find % of values that are les than average selected. (User can filter with category and average have to be recalculate)
So I make some calculations and have this.
Trying to achieve the target i created two measures. the first one calculate the average over all selected values and the second one i try to make a flag when the value was less than average.
Average = CALCULATE( [Total]/[Qty] ,ALLSELECTED(Test) )
Flag = IF([Total]< [Average],0,1)
Doing the maths on my excel this is what I have to get.
What we need is calculate "% of values are less than avg". I was thinking about sum flag when is 1 and then divide over total count but when I tried to sum flag value it resolve as 1. Do you have an idea how to accomplish this? Maybe with a different way
Thanks in advance
PBIX attached
Solved! Go to Solution.
Hi, use a measure
SumFlag = SUMX(Test,IF([Flag]=1,1,0))
And
% of Values are less than average = DIVIDE([SumFlag],[Qty])
Regards
Victor
Lima - Peru
Hi, use a measure
SumFlag = SUMX(Test,IF([Flag]=1,1,0))
And
% of Values are less than average = DIVIDE([SumFlag],[Qty])
Regards
Victor
Lima - Peru
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 |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |