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:

 

OidAssayQuantityDate
1As0.5746331/1/2016 0:00
2Ag8563.6381/2/2016 0:00
3Au3122.6661/3/2016 0:00
4S240.01461/4/2016 0:00
5Cu3.2078821/5/2016 0:00
6Ag64222.551/6/2016 0:00
7As17.165471/7/2016 0:00
8Cu57.415551/8/2016 0:00

 

Thank you in advance :)

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

2 Replies

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