New Measure - Percentage based on two quantities

Question

Morning All,&nbsp;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:&nbsp;Percentage = DIVIDE([ExpectedQuantity], [CountedQuantity])&nbsp;

anonymous · Answer

Hi&nbsp;Anonymous&nbsp;,
&nbsp;
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)

&nbsp;
&nbsp;
&nbsp;&nbsp;&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
Best Regards,
Stephen Tao
&nbsp;
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly. &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;
&nbsp;

anonymous · Answer

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

ajendra · Answer

Hello,I hope&nbsp; you are doing well!&nbsp;I have gone through your query along with the sahred picture of data.&nbsp;As far as i know in this filter contex the desired output is not achievable.&nbsp;If you are able to try other way to achive it please try that.&nbsp;thanks,Ajendra&nbsp;

anonymous · Answer

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

Forum Discussion

New Measure - Percentage based on two quantities

4 Replies

Related content

Percentage with Count Quantity

Odd Percentage Measure Behavior

Percentage by group

Quick measure - Percentage difference

Percentage calcualtiion for DAX Measures