cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
iamtrangdoan Regular Visitor
Regular Visitor

Set multiple quantity threholds for products & show ONLY over-the-threshold months

Hi everyone,

 

I have the following data:

 

1. "Full Data" that has the following columns:

 

Vendor NameProduct IDPosting DateOrder QuantityNet Oder QuantityEngine ModelFinal Run Time

 

 

2. "Compare Data" that has the following columns:

VendorIDModelThreshold

 

*There are only a few IDs in the "Compare Data" table, which might or might not appear in the "Full Data" table.

 

Sample Data can be found here:

https://liveconcordia-my.sharepoint.com/:x:/g/personal/t_oa_live_concordia_ca/EZvVii_szsBBnYOSBkK0n0...

 

I'm looking to:

 

  • calculate Monthly Average Order Quantity of each product ID in the "Compare Data" table, using "Order Quantity" column from the "Full Data" table

 

  • create a visual where it can tell me in which months these product IDs's quantity go over the set thresholds (exclude all months that have 'normal' monthly order quantities)

 

Ex: Let's say for the product ID below (1005-1), the order quantity threshold is 10 units/month. There're 4 months where the threshold was surpassed: March, October, November and December.

 

How would you calculate the Monthly Average Quantity and then show only the 4 said months in a visual?

 

000.JPG

 

It looks like data alert to me but not quite that way because I already tried the data alert feature and it just didn't work out for in my case. 

 

Can anyone help me with this please? How would you go about my problem?

 

Thank you thank you thank you!

2 REPLIES 2
Community Support Team
Community Support Team

Re: Set multiple quantity threholds for products & show ONLY over-the-threshold months

Hi @iamtrangdoan,

Based on my test, you could refer to below steps:

Create a calculated column for your data model:

Column = IF('Table1'[AVERAGE QUANTITY]>10,"Show",BLANK())

1.PNG

Create a measure:

Average = CALCULATE(AVERAGE(Table1[AVERAGE QUANTITY]),ALL(Table1))

Set a page level filter and choose the "Show":

1.PNG

Now you could see the result:

1.PNG

If you do not want to set the page filter, you could also create below measure:

Show Measure = CALCULATE(SUM(Table1[AVERAGE QUANTITY]),FILTER('Table1','Table1'[Column]="Show"))

Result:

1.PNG

You could also download the pbix file to have a view.

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
iamtrangdoan Regular Visitor
Regular Visitor

Re: Set multiple quantity threholds for products & show ONLY over-the-threshold months

Hi @v-danhe-msft, thank you for your response! However, is there a way to calculate the monthly average quantity for each product ID in the "Compare Data" table? Because I had to manually create and calculate the "Average Quantity" table which will be time-consuming when my real data has, let's say, 500 different IDs.

 

How would you go about it?

 

Thank you thank you!