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

1 ACCEPTED SOLUTION
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!

2 REPLIES 2
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!

New Member

Great Thanks

## Helpful resources

Announcements

#### Welcome to the User Group Public Preview

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

#### Check it Out!

Click here to read more about the March 2021 Updates!

#### 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.

#### Experience what’s next for Power BI

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

Top Solution Authors
Top Kudoed Authors