topic Re: New Measure - Percentage based on two quantities in DAX Commands and Tips

New Measure - Percentage based on two quantities

Anonymous — Wed, 26 Apr 2023 10:16:32 GMT

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])

Re: New Measure - Percentage based on two quantities

Anonymous — Fri, 28 Apr 2023 06:06:49 GMT

Hi Anonymous ,

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.

Re: New Measure - Percentage based on two quantities

Anonymous — Fri, 28 Apr 2023 08:30:54 GMT

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)))."

Re: New Measure - Percentage based on two quantities

Ajendra — Fri, 28 Apr 2023 20:58:15 GMT

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

Re: New Measure - Percentage based on two quantities

Anonymous — Tue, 02 May 2023 16:53:16 GMT

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])