Get the Mean value in a list (Average isnt working)

[DebbieE]

I have a SUM(cas) as a Measure

And I also have a AVERAGE(Cas) Measure as above.

And I need to display the mean value from the Sum which should be around 347

I already have a filter applied on the years to get those years above on the date table

But I cant come up with DAX that does this

I eventually want to display it in a card so It will need to have the filter applied to it for the number of years but then show that mean value of Cas 

[DataInsights]

@DebbieE,

Try these measures. [Mean Value] averages the yearly sums.

Sum Cas = SUM ( AverageTest[Cas] )

Mean Value = 
VAR vBaseTable =
    ADDCOLUMNS ( VALUES ( Dates[Year] ), "tmpSumCas", [Sum Cas] )
VAR vResult =
    AVERAGEX ( vBaseTable, [tmpSumCas] )
RETURN
    vResult

[DebbieE]

Im getting 69.84 not 347. Is there anyway to add into the vbase table a filter on Area = "UK" and a BaseFlag = true in the date table? This is because there are only 5 years used for this Baseline average.

I ttied adding these in a CALCULATE to

vResult but it hasnt made a difference

[DataInsights]

@DebbieE,

Try this, with a slicer for Area.

Mean Value = 
VAR vBaseTable =
    ADDCOLUMNS (
        CALCULATETABLE (
            VALUES ( Dates[Year] ),
            Dates[Base Flag] = "True"
        ),
        "tmpSumCas", [Sum Cas]
    )
VAR vResult =
    AVERAGEX ( vBaseTable, [tmpSumCas] )
RETURN
    vResult

[DebbieE]

So nearly there. This works perfectly as my card. However I use this in some more DAX that wants to use it as a single value but the filter for Dates are being applied for this one too. Is there any way I can disconnect this value so other subsequent DAX can use it just as a number?

This is the next bit of DAX its being applied in

Targets for column chart =

VAR LatestYr =

CALCULATE(

MAX('Date'[Year]),

ALLSELECTED('Date'[Year])

)

VAR EarliestYr =

CALCULATE(

MIN('Date'[Year]),

ALLSELECTED('Date'[Year])

)

VAR breakdownPart =

DIVIDE([Target] - [Baseline Average], LatestYr - EarliestYr)

RETURN

[Baseline Average] + breakdownPart * (MAX('Date'[Year]) - EarliestYr)

And I create a clustered column chart with dates from 2018 to 2033

[DataInsights]

@DebbieE,

Glad to hear the measure works for the card visual.

Regarding the second request, it sounds as if you want the user to select the years in the Date slicer, instead of using Dates[Base Flag] = "True". If that is the case, you would need a separate measure since the measure I provided is designed to use Dates[Base Flag] = "True".

[DebbieE]

Im not quite sure. the measure is correct with the Base flag = true. But the second measure needs to use this measure as it its just a number, completely independant on all the filters etc set for that measure. It works perfectly if you create a measure of say = 346. So I need the lower level one to work in the 2nd of DAX as if it was a hardcoded value

[DataInsights · Accepted solution]

@DebbieE,

You could change the filter context to use the slicer years. In your subsequent DAX, add a variable:

VAR BaselineAvg =
CALCULATE (
    [Baseline Average],
    FILTER (
        ALL ( 'Date' ),
        'Date'[Year] >= EarliestYr
            && 'Date'[Year] <= LatestYr
    )
)

Use this variable BaselineAvg in your calculation:

VAR breakdownPart =
DIVIDE([Target] - BaselineAvg, LatestYr - EarliestYr)

[DebbieE]

Mine only comes to 69.85 instead of 346 even though im doing in in the same way above?

I dont understand the DAX enough to understand why this is