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 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:

Value1Value2Aggregation
111
101
1-1-1
000
0-1-1
-1-1-1
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
)

2 Replies

  • johnt75's avatar
    johnt75
    Super User
    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]
                )