Summarize a summarized table

[Cado_one]

Hi everyone,

Context : I need to display values with different aggregations in a matrix on 4 levels.

- Level 1 : raw values from a source table

- Level 2 : sum of the level 1 values by month for a location

- Level 3 : average of the level 2 values for a location

- Level 4 : average of the level 3 values (no filter, it corresponds to the bottom total line)

I face an issue in this measure :

Measure = 

VAR Table1 = SUMMARIZE(Source, [LOCATIONS], [MONTH], "SumMonths", SUM(Source[Values]))
VAR Table2 = SUMMARIZE(Table1, [LOCATIONS], "AverageLocations", AVERAGE([SumMonths]))

RETURN
IF(
    ISINSCOPE(Dates[Month]),
    SUM(Source[Values]),
    IF(
        ISINSCOPE(Source[LOCATIONS]),
        AVERAGEX(Table1, [SumMonths]),
        AVERAGEX(Table2, [AverageLocations])
    )
)

The values returned by the measure are correct in the matrix for level 1, 2 and 3 but then I have an error with the AVERAGE([SumMonths]) at the end of the second VAR. It doesn't recognize the summarized column of the Table1.

Does anyone has an idea of how this could be handled ?

Thanks in advance,

Cado

[v-shex-msft · Accepted solution]

Hi @Cado_one,

You can try to use the following measure formulas if helps:

Formula =
VAR t1 =
    SUMMARIZE ( Source, [LOCATIONS], [MONTH], "SumMonths", SUM ( Source[Values] ) )
VAR t2 =
    SUMMARIZE (
        Source,
        [LOCATIONS],
        "AverageLocations",
            AVERAGEX (
                FILTER ( t1, [LOCATIONS] = EARLIER ( Source[LOCATIONS] ) ),
                [SumMonths]
            )
    )
RETURN
    IF (
        ISINSCOPE ( Dates[Month] ),
        SUM ( Source[Values] ),
        IF (
            ISINSCOPE ( Source[LOCATIONS] ),
            AVERAGEX ( t1, [SumMonths] ),
            AVERAGEX ( t2, [AverageLocations] )
        )
    )

If the above formula also doesn't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Cado_one]

Hi @v-shex-msft 

it's working thanks !

Best regards,

Cado