Forum Discussion

Quakker's avatar
Quakker
Helper II
3 years ago

Filter on multiple Columns / Values - Data Consolidation

Hi All, i´m struggeling to create a Costcenter Report, where some kind of consolidation has to be done. Attached you can see some sample data.   What i want to achieve is: If a User selects...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

HI Quakker,

You can try to use the following measure formula to get the different based on current COSTCENTER group:

formula =
VAR currCostcenter =
    SELECTEDVALUE ( Table[COSTCENTER] )
RETURN
    IF (
        currCostcenter = BLANK (),
        SUM ( Table[Amount] ),
        VAR _path =
            SUBSTITUTE ( currCostcenter, "-", "|" )
        RETURN
            CALCULATE (
                SUM ( Table[Amount] ),
                FILTER ( 'Table', Table[SOURCEOBJECT] & "" = PATHITEM ( _path, 1 ) )
            )
                - CALCULATE (
                    SUM ( Table[Amount] ),
                    FILTER ( 'Table', Table[SOURCEOBJECT] & "" = PATHITEM ( _path, 2 ) )
                )
    )

Regards,
Xiaoxin Sheng