Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
I'm looking to:
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!
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
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!
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |