Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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




SF2023_0-1682504006109.png

 




4 REPLIES 4
SF2023
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])
Ajendra
Resolver I
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

 

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

v-stephen-msft
Community Support
Community Support

Hi @SF2023 ,

 

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

vstephenmsft_0-1682661483184.png

vstephenmsft_1-1682661516978.png

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

vstephenmsft_2-1682661575673.png

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)

vstephenmsft_3-1682661975236.png

 

 

   

                                                                                                                                                         

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.           

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors