cancel
Showing results for
Did you mean:
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 Name Product ID Posting Date Order Quantity Net Oder Quantity Engine Model Final Run Time

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

 Vendor ID Model Threshold

*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?

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

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

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())`

Create a measure:

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

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

Now you could see the result:

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:

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.
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!