Forum Discussion

GeorgiBarov's avatar
GeorgiBarov
Frequent Visitor
8 years ago
Solved

Average YTD by category

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 ...
Need Help
  • v-piga-msft's avatar
    v-piga-msft
    8 years ago

    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

v-piga-msft's avatar
v-piga-msft
Icon for Resident Rockstar rankResident Rockstar
8 years ago

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's avatar
GeorgiBarov
Frequent Visitor
8 years ago

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