Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jpereztang
Helper I
Helper I

% 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
Vvelarde
Community Champion
Community Champion

@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

View solution in original post

2 REPLIES 2
jpereztang
Helper I
Helper I

Eso fue la cereza del pastel, gracias @Vvelarde ! 

 

Saludos,

 

Vvelarde
Community Champion
Community Champion

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.