Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi guys,
How can I calculate the average year to date Quantity by Assay?
I have table like:
Oid | Assay | Quantity | Date |
1 | As | 0.574633 | 1/1/2016 0:00 |
2 | Ag | 8563.638 | 1/2/2016 0:00 |
3 | Au | 3122.666 | 1/3/2016 0:00 |
4 | S | 240.0146 | 1/4/2016 0:00 |
5 | Cu | 3.207882 | 1/5/2016 0:00 |
6 | Ag | 64222.55 | 1/6/2016 0:00 |
7 | As | 17.16547 | 1/7/2016 0:00 |
8 | Cu | 57.41555 | 1/8/2016 0:00 |
Thank you in advance 🙂
Solved! Go to Solution.
Hi @GeorgiBarov,
You could try to create the measure below to get your desired output.
average_ytd = TOTALYTD ( AVERAGE ( demo[Quantity] ), 'Table'[Date], FILTER ( ALL ( demo ), 'demo'[Assay] = MAX ( 'demo'[Assay] ) ) )
The output is below.
If you need additional help please share your expected output.
Best Regards,
Cherry
Hi @GeorgiBarov,
You could try to create the measure below to get your desired output.
average_ytd = TOTALYTD ( AVERAGE ( demo[Quantity] ), 'Table'[Date], FILTER ( ALL ( demo ), 'demo'[Assay] = MAX ( 'demo'[Assay] ) ) )
The output is below.
If you need additional help please share your expected output.
Best Regards,
Cherry
Hi @v-piga-msft and thank you for the reply
I tried your formula but it calculate the AVG([Quantity]) only if there is one [Quantity] per day.
If we have a few records of Quantity related to a one day the formula is wrong.
In the table above Quantity is automaticaly summurized, but actualy there are a few records per day
Here are the examples:
Expected value on 01-DEC-2016 / average_ytd is: 205 not 102.5
Thank you 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |