Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 _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 () )
Solved! Go to Solution.
@Nelson-Cheng ,
try like
Cost Save new = sumx(Values(Table[Item]),[Cost Save])
@Nelson-Cheng ,
try like
Cost Save new = sumx(Values(Table[Item]),[Cost Save])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |