cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

New Member

## New Measure - Percentage based on two quantities

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

4 REPLIES 4
New Member

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

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

New Member

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

Community Support

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.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors