Forum Discussion

lokosrio's avatar
lokosrio
Helper II
3 years ago
Solved

Calculation at higher level

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 loo...
Need Help
  • johnt75's avatar
    johnt75
    3 years ago

    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
)
lokosrio's avatar
lokosrio
Helper II
3 years ago

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]
                )