cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## 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
Super Contributor

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

Hi @Anonymous,

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:

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
Anonymous
Not applicable

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

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 274 members 3,154 guests
Recent signins: