Average YTD by category

[GeorgiBarov]

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 🙂

[v-piga-msft · Accepted 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

[GeorgiBarov]

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 🙂