cancel
Showing results for 
Search instead for 
Did you mean: 
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 IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

2 REPLIES 2
amitchandak
Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

@amitchandak 

Great Thanks

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.