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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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 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
v-danhe-msft
Employee
Employee

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

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

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!

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.