cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jpereztang Regular Visitor
Regular Visitor

% of values that are less than the average

Hi everyone,

 

Theres kind of complicated scenario. This is my test dataset.

 

dataset.png

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.

average y flag.png

 

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.

 

excel.png

 

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: % of values that are less than the average

@jpereztang

 

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




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




2 REPLIES 2
Super User
Super User

Re: % of values that are less than the average

@jpereztang

 

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




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Highlighted
jpereztang Regular Visitor
Regular Visitor

Re: % of values that are less than the average

Eso fue la cereza del pastel, gracias @Vvelarde ! 

 

Saludos,