Need help with Measure please

[KevBI]

Hello Guys,

I have following Table 

The Column I am having problems with is the marked AOPWD Column.

It is a calculated column : 

AOPWD = 'Spoilage AOP 2022'[AOP] / 'Spoilage AOP 2022'[WDMonth] * [workdays]

So as you can see, the values in AOPWD should be different than the AOP Column, because it should get divided by 20(WDMonth) and multiplied by 7(Workdays) = Measure .

I think that I need to convert the calculated column AOPWD into a Measure, but I can't seem to get it working.

[KevBI]

@amitchandak 
So when looking at the first row, Value Stream CE-E, filtered on 01.02-09.02,

we have an AOP of 60900, WDMonth = 20 and workdays = 7.

So it should calculate 60900 / 20 * 7 = 21315
The Measure puts out 435 though.

[v-rzhou-msft · Accepted solution]

Hi @KevBI ,

I think there is something wrong in your calculate logic.

The calculate logic of your measure is 60900 / (20 * 7) = 435.

Try this code.

AOPWDMeasure =
DIVIDE (
    SUM ( 'Spoilage AOP 2022'[AOP] ),
    SUM ( 'Spoilage AOP 2022'[WDMonth] )
) * ( [workdays] )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@KevBI , Try like, if workday is measure

AOPWD = Divide(sum('Spoilage AOP 2022'[AOP]) ,sum( 'Spoilage AOP 2022'[WDMonth]) * [workdays]

or

AOPWD = Divide(sum('Spoilage AOP 2022'[AOP]) ,sum( 'Spoilage AOP 2022'[WDMonth]) * max([workdays])

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[KevBI]

Thanks @amitchandak !

It doesn't calculate correctly though, maybe I am doing something worng.

I attached a sample Report, thank you for helping out!
https://1drv.ms/u/s!Aq582f-TWdgxi2jvJdiLbdfcRHHK?e=czCfaz 

[Ashish_Mathur]

Hi,

Modify your measure to

AOPWDMeasure = Divide(sum('Spoilage AOP 2022'[AOP]) ,sum( 'Spoilage AOP 2022'[WDMonth])) * ([workdays])

Hope this helps.

[amitchandak]

@KevBI , what is the expected number. as number seem fine to me

there should small diff in the calculation

AOPWDMeasure = Divide(sum('Spoilage AOP 2022'[AOP]) ,sumX('Spoilage AOP 2022', 'Spoilage AOP 2022'[WDMonth] * [workdays]))