Calculation at higher level

[lokosrio]

hi,

I have a calculation like below:

KPI =
SWITCH ( MAX ( 'Fact'[Color] ),
"Green", 1,
"Grey", 0,
"Red", -1
)

For each line I have assigned a color (green, grey or red), so it looks like this:

But I want to change the formula, so when lower level will have 0 and 1 then total should be 1.

For example:

Line16 = 0

Line15 = 1

Total for those lines should be 1 (now it is 0).

Here is the list of all cases:

Value1	Value2	Aggregation
1	1	1
1	0	1
1	-1	-1
0	0	0
0	-1	-1
-1	-1	-1

[lokosrio]

works as expected, thank you very much!

I have made 2 fixes in the TREATS function:

- added brackets to avoid In the function TREATAS the number of output columns (2) does not match the number of columns in the input table (1) error

- swapped variables val1 with val2  in to search for proper values from Aggregation table

After the changes it looks like this:

TREATAS (
                    { ( val2 , val1 ) },
                    'Aggregation table'[Value 1],
                    'Aggregation table'[Value 2]
                )

[johnt75 · Accepted solution]

I think this will work

Green Factor =
SWITCH (
    TRUE (),
    ISINSCOPE ( 'Table'[Line ID] ), [KPI],
    ISINSCOPE ( 'Table'[Department ID] ),
        VAR summaryTable =
            ADDCOLUMNS ( VALUES ( 'Table'[Line ID] ), "@val", [KPI] )
        VAR val1 =
            MINX ( summaryTable, [@val] )
        VAR val2 =
            MAXX ( summaryTable, [@val] )
        VAR result =
            CALCULATE (
                MAX ( 'Aggregation table'[Aggregation] ),
                TREATAS (
                    { val1, val2 },
                    'Aggregation table'[Value 1],
                    'Aggregation table'[Value 2]
                )
            )
        RETURN
            result,
    ISINSCOPE ( 'Table'[Area ID] ),
        VAR summaryTable =
            ADDCOLUMNS ( VALUES ( 'Table'[Department ID] ), "@val", [KPI] )
        VAR val1 =
            MINX ( summaryTable, [@val] )
        VAR val2 =
            MAXX ( summaryTable, [@val] )
        VAR result =
            CALCULATE (
                MAX ( 'Aggregation table'[Aggregation] ),
                TREATAS (
                    { val1, val2 },
                    'Aggregation table'[Value 1],
                    'Aggregation table'[Value 2]
                )
            )
        RETURN
            result,
    ISINSCOPE ( 'Table'[Site ID] ),
        VAR summaryTable =
            ADDCOLUMNS ( VALUES ( 'Table'[Area ID] ), "@val", [KPI] )
        VAR val1 =
            MINX ( summaryTable, [@val] )
        VAR val2 =
            MAXX ( summaryTable, [@val] )
        VAR result =
            CALCULATE (
                MAX ( 'Aggregation table'[Aggregation] ),
                TREATAS (
                    { val1, val2 },
                    'Aggregation table'[Value 1],
                    'Aggregation table'[Value 2]
                )
            )
        RETURN
            result,
    VAR summaryTable =
        ADDCOLUMNS ( VALUES ( 'Table'[Site ID] ), "@val", [KPI] )
    VAR val1 =
        MINX ( summaryTable, [@val] )
    VAR val2 =
        MAXX ( summaryTable, [@val] )
    VAR result =
        CALCULATE (
            MAX ( 'Aggregation table'[Aggregation] ),
            TREATAS (
                { val1, val2 },
                'Aggregation table'[Value 1],
                'Aggregation table'[Value 2]
            )
        )
    RETURN
        result
)