New Measure - Percentage based on two quantities

[SF2023]

Morning All,

I've used commnity support in the past on things such as VBA and again more recently following a D365 go-live and learning to link PBI and Dynamics..

From pulling a warehouse cycle counting table into PowerBI, I am then trying to add in a column that will give me the percentage for each count. I have managed to return a figure using DIVIDE. However I will need additional conditions if anyone is able to help please.

If you expect say 10pcs, but count 11, I would need this to instead return 90% accuracy, rather than 110%.

So far, the formula used is: 

Percentage = DIVIDE([ExpectedQuantity], [CountedQuantity])

[SF2023]

So the solution I have managed to get to work is:

Measure 1

Accuracy =

 VAR OnHand = SUM(CycleCountingWarehouseWorkLinesV3[ExpectedQuantity])

 VAR CountedQuantity = SUM(CycleCountingWarehouseWorkLinesV3[CountedQuantity])

 VAR MinValue = MIN(OnHand, CountedQuantity)

 VAR MaxValue = MAX(OnHand, CountedQuantity)

 VAR Accuracy = IF(MaxValue = 0,0,DIVIDE(MinValue, MaxValue, 0))

RETURN Accuracy * 100


Measure 2

AverageAccuracy = Averagex(CycleCountingWarehouseWorkLinesV3,[Accuracy])

[Ajendra]

Hello,

I hope  you are doing well!

I have gone through your query along with the sahred picture of data.

As far as i know in this filter contex the desired output is not achievable.

If you are able to try other way to achive it please try that.

thanks,

Ajendra

[SF2023]

Hi, thanks for the above! I have used the measure (also posted below) but returns:

"The syntax for 'return' is incorrect. (DAX(var_CountedQuantity=CALCULATE(COUNT(CycleCountingWarehouseWorkLinesV3[ExpectedQuantity]),FILTER((ALLSELECTED(CycleCountingWarehouseWorkLinesV3[CountedQuantity]=MAX(CycleCountingWarehouseWorkLinesV3[ExpectedQuantity])))
return DIVIDE([ExpectedQuantity Value],_CountedQuantity)))." 

[v-stephen-msft]

Hi @SF2023 ,

If you want a dynamic [ExpectedQuantity], you can try what-if parameters.

After the what-if parameter is created, you get a dynamic [ExpectedQuantity] that can be modified by slicers.

Here's the measure to calculate the percentage.

Percentage = 
var _CountedQuantity=CALCULATE(COUNT('Table'[ID]),FILTER(ALLSELECTED('Table'),[ID]=MAX('Table'[ID])))
return DIVIDE([ExpectedQuantity Value],_CountedQuantity)

Best Regards,

Stephen Tao

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