Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |