Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |