Average over filtered table

[JL_007]

I'm trying for some time already to get a DAX trick done. It might be very simple, but I'm just not succeeding...

I've got this dataset:

Year	Month	Type	Value
Current	1	A	50
Current	1	B	60
Current	2	A	100
Current	3	A	20
Current	4	A	30
Current	5	A	60
Current	6	A	70
Current	7	B	50
Current	7	A	90
Previous	1	A	100
Previous	2	B	20
Previous	3	A	30
Previous	4	A	60
Previous	5	A	70
Previous	6	A	90
Previous	7	A	50
Previous	8	A	100
Previous	9	A	20
Previous	10	B	30
Previous	11	A	60
Previous	11	B	60
Previous	12	B	70

I've got slicers on Month & Type.

I would like to have a Line and stacked column chart, where one column represents the current year value per month, the other column the previous year value per month.

The Line should represent the average of values of the current year, taking in to account all the filtering in the slicers. The average is expected to be a horizontal line.

I managed to do the columns, but I'm really struggling with the average line.

- CALCULATE always keep taking 'Month' into account, causing my average to be equal to my actual.

- CALCULATETABLE removes the filter context, so it doesn't keep the filter values from the slicers.

Any help or suggestions would be appreciated!

Thanks in advance!

[OwenAuger · Accepted solution]

Hi @JL_007 

I've attached an example PBIX of what I think you're looking for.

You can make use of the ALLSELECTED function to restore the "overall" filter context of the visual (for measures displayed directly in the visual).

In my example PBIX, I created these measures:

Value Sum = 
SUM ( Data[Value] )

Current Year Monthly Average = 
CALCULATE (
    AVERAGEX (
        VALUES ( Data[Month] ),
        [Value Sum]
    ),
    ALLSELECTED (),
    Data[Year] = "Current"
)

In Current Year Monthly Average, ALLSELECTED () is applied to restore the overall filter context of the visual, and Year = "Current" is applied as an additional filter. With these filters applied, the monthly average is then calculated using AVERAGEX.

Note that months with no values (where [Value Sum] evaluates to blank) are ignored by AVERAGEX.

Does something like this work for you?

Regards

[JL_007]

Well, that's just a perfect solution. Works like a charm!! Thank you, Owen!