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

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
Nelson-Cheng
New Member

Calculate average value from multiple measures, and then exclude the outliner

I have a list of items cost from different manufacturer & in different currency.

So i create a measure to convert each manufacturing cost with dynamic currency conversion. But i need to compare the manufacturer cost item by item to get each item's average cost.

And then base on the average manufacturer cost to set a threshold if any manufacturer cost is above or below 50% compare to the average manufacturer cost to be excluded. Then recalculate the NEW average manufacturer cost.

With the NEW average manufacturer cost, i compare it with the lowest manufacturer cost and try to get the cost saving item by item.

 

I create the below formula, it is able to calculate and display in a Table one by one.

But it turns out the total in the table format or in the summarize card format visual, it is not correct as the calculation will calculate againe in the total level......

 

Can anyone help for this case?

 

What i have already: 

Manufacturer A cost = [CostA]

Manufacturer B cost = [CostB]

Manufacturer C cost = [CostC]

 

What i try to do for the measure to get the New average to excl 50% above / below : 

//to exclude if the manufacturer didn't provide with cost

VAR _costcount = INT ( [Cost A] <> BLANK () )+ INT ( [Cost B] <> BLANK () )+ INT ( [Cost C] <> BLANK () )

VAR _threshold = DIVIDE ( [Cost A] + [Cost B] + [Cost C] , _costcount)

VAR _upperthreshold = _threshold*1.5
VAR _lowwerthreshold = _threshold*0.5
VAR _CostA = IF( [Cost A] <> BLANK(), IF(AND([Cost A]>=_lowwerthreshold , [Cost A]<=_upperthreshold), [Cost A]))

VAR _CostB = IF( [Cost B] <> BLANK(), IF(AND([Cost B]>=_lowwerthreshold , [Cost B]<=_upperthreshold), [Cost B]))

VAR _CostC = IF( [Cost C] <> BLANK(), IF(AND([Cost C]>=_lowwerthreshold , [Cost C]<=_upperthreshold), [Cost C]))

VAR _newcostcount = INT ( _CostA<> BLANK () )+ INT ( _CostB <> BLANK () )+ INT ( _CostC <> BLANK () )

RETURN
DIVIDE (_CostA +_CostB +_CostC, _newcostcount )
 
 
Check the saving between Lowest Cost with New Average Cost
Cost Save = [Average excl 50% above & below] - 'Factory Cost'[CostA]
 

Test.jpg

 

 
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Nelson-Cheng ,
try like
Cost Save new = sumx(Values(Table[Item]),[Cost Save])

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@Nelson-Cheng ,
try like
Cost Save new = sumx(Values(Table[Item]),[Cost Save])

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

@amitchandak 

Great Thanks

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.