How to change the measure when drilling down in bar chart

[MartinVollaard]

Hi All,

I'm stuck and tried many provided solutions but can't get it fixed. I am just starting so please help me. I have a bar chart with 2 axis (year and then you can drill down to months). On the month level I sum a column in my datafield  - SUM(Sheet[FIELD]). It provides me with the right number per month. On the year level it sums all underlying months but on that level I need an average.

Monthly basis (MAAND):

Yearly basis (Here I need an average) Not a sum of 12 months.

This isn't working. I've tried many variations on the measure mentioned below. I'm lost

Measure = IF(ISFILTERED(Sheet[YEAR]) ; SUM(Sheet[FIELD]); SUM(Sheet[FIELD])/12)

Many thanks in advance for your help and guidance.

[Ashish_Mathur · Accepted solution]

Hi,

Try this

=IF(HASONEVALUE(Sheet[Month]),SUM(Sheet[FIELD]),SUM(Sheet[FIELD])/12)

[Anonymous]

I'm trying to do a similar thing, but with daily data rolling up to a monthly and yearly level.

For the lowest (daily) level, I want the bar chart to show the sum of items in each category each day.

For the next (monthly) level, I want it to show the average for the month in each category.

For the top (yearly) level, I want to see the average for the year in each category.

How do I do this?

Thanks in advance!

[Anonymous]

@Anonymous  I'm trying to do something similar.  Did you ever figure out a solution?

Cheers!

[MartinVollaard]

Thank you very much!! This is the solution.

[Ashish_Mathur]

You are welcome.