How to select the value based on rank

[Power_Mate]

Hi All,

I am looking for the help on finding the right cost based on a another column.

Product_Id	Product_Cd	Product_Type	Cost
X00	470156	DED	1459
Y00	470156		324
Y00	470156	SED	657
Y00	470156	LED	23
Z01	32789		78
C21	127835	DED	567
C21	127835		3217
Y00	127835	SED	1789
F01	127835	LED	368
S01	697431	LED	3214
D10	6974321	ZED	1145
Y00	6974321		567
Z01	6974321	LED	1145
F01	6974321		987
MZ1	6974321	FED	1792
N19	333247	ZED	1792
X00	333247		1792
Y00	333247	SED	1792
Z01	333247		1478
C21	333247		328
C21	333247		679
Y00	976179	SED	34789
F01	976179	LED	14568
S01	976179		35789
D10	976179		3247
Y00	976179	CED	3247
Z01	2749513		0
F01	2749513		0
MZ1	2749513	ZED	3256
N19	2749513	LED	0
M99	2749513	SED	7894
Q61	2749513		0

Above you can find the data related to the issue.

as part of the requirement I need to pick the cost value for each product_cd based on Product_type.

In product_type we have multiple values out of it we need to consider only 3.

On priority based we need pick ZED value of Product, If ZED value is not available then we need to pick SED value and if SED is not available then LED else 0. 

If we have all ZED,SED  and LED values we need to pick ZED.

For Example 1:

Product_Id	Product_Cd	Product_Type	Cost
X00	470156	DED	1459
Y00	470156		324
Y00	470156	SED	657
Y00	470156	LED	23

for Product_cd 470156 cost is 657.

Example 2 :

Product_Id	Product_Cd	Product_Type	Cost
Z01	2749513		0
F01	2749513		0
MZ1	2749513	ZED	3256
N19	2749513	LED	0
M99	2749513	SED	7894
Q61	2749513		0

for 'Product_cd' 2749513 Cost is 3256

All this should be happend dynamically by using DAX.

Thanks In advance.

Regards

Power Mate

[Power_Mate]

Do are we missing the third one i see only ZED and SED, or do we need to write one more IF for LED.

Can you please correct me if i am wrong

[Power_Mate]

Thanks for the help. Its matches for the requirement

[mohammedadnant · Accepted solution]

Hi @Power_Mate 

Please try the following

FindCost = 

//[SumCost] = sum('Product Cost'[Cost]) <--- ANOTHER FORMULA 

VAR _ZEDCost = CALCULATE( [SumCost], ALL('Product Cost'[Product_Type]), 'Product Cost'[Product_Type]="ZED")

VAR _SEDCost = CALCULATE( [SumCost], ALL('Product Cost'[Product_Type]), 'Product Cost'[Product_Type]="SED")

VAR _LEDCost = CALCULATE( [SumCost], ALL('Product Cost'[Product_Type]), 'Product Cost'[Product_Type]="LED")

RETURN

IF ( ISBLANK(_ZEDCost), 

IF(ISBLANK(_SEDCost), _ZEDCost,_SEDCost), _ZEDCost)