Seemingly simple CALCULATE OR statement

[sdas028]

Hi, I need help with what I thought was going to be a simple calculated measure. 

The data I have has three columns that I need in a larger table: one for Production Area, one for Cost, and one for Trays Packed - what I would like is to calculate the cost per tray for two production areas RK and SP - each of these production areas has their own costs against them however the Trays Packed are only under RK so when I do a simple cost/trays calculation nothing comes up for SP. What I want is SP $/RK Trays effectively. I tried creating a measure for Trays Packed = SUM(Trays Packed) and then dividing cost by that but it still comes up blank. I then tried a CALCULATE (SUM(cost), FILTER(production Area = "RK" OR "SP"))/Repack Trays but this didnt work either. I have the cost and trays packed in a matrix so each time I put the measure into the table it has values under RK but nothing or infinity under SP.

Hopefully that all made sense!

[v-piga-msft]

Hi @sdas028,

If it is convenient, could you please share some data smple and your desired output so that I could understand your scenario better and get the solution?

Best  Regards,

Cherry

[sdas028]

Hi Cherry,

Apologies for the delay in replying. Here is a really simple example of the data I have:

Labour Cost	Trays packed	Production Area
$100	1000	CCK
$200	2000	RK
$150	1500	RK
$100	1000	RK
$250	2500	RK
$50		SP
$150		SP
$200		SP
$250		SP

Basically I want to calculate the total cost of each production type but because SP doesnt have any trays packed against it I want it to be calculated on the RK trays

Hope that helps

Thanks,

Sophie

[v-piga-msft]

Hi @sdas028,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please share your desired output.

Best Regards,

Cherry

[sdas028]

Hi Cherry,

I understand the concept of the PowerBI community, thank you.

I dont always have time to check my powerBI notifications as this isn't the only thing I am working on.

I have accepted your answer as the solution for your convenience.

[Thejeswar · Accepted solution]

Hi @sdas028,

I understand that you want to get the Trays Packed of Production Area of RK, when the Production Area is SP where it is NULL actually.

You can use the following DAX statements to meet your requirement

1. Create the following Calculated Column

Trays Packed = IF(Data[Production Area] = "SP", CALCULATE(AVERAGEX(Data,Data[Trays packed]),FILTER(Data,Data[Production Area]="RK")), Data[Trays packed])

The Above column with have No. of Trays packed for all the Production Areas

2. now create Cost per Tray calculated column as shown below

Cost per Tray = Data[Labour Cost]/ Data[Trays Packed]

The Resulting output will look as shown below

Hope this helps!!!