Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Conditional classification

Hi guys, couldn't find a solution for my issue. 

 

I work in Controlling and I have a mapping to sort different expenses types in our ledger accounts. Mainly the mapping follows directly the ACCOUNTS, but there are some exceptions where, depending on the COST CENTER, it should be classified in a different way. 

 


So, two questions:

1 - Is there a straight forward way of doing this using "regular" tools in Power BI?

 

2 - The only (long and winding) way I can think of is merging tables and creating custom calculated columns. But before trying this, considering best practices, when should I cross the line between only relating tables to start merging them? 

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi Anonymous ,

    According to your description, here's my solution.

    Create a measure.

    New Values =
VAR _A =
    SUMX (
        FILTER (
            ALL ( 'Ledger' ),
            'Ledger'[Cost Center] = "A"
                && 'Ledger'[GL Account] <> 3
        ),
        'Ledger'[Values]
    )
VAR _Other =
    SUMX (
        FILTER (
            ALL ( 'Ledger' ),
            'Ledger'[Cost Center] = "A"
                && 'Ledger'[GL Account] <> 3
                && 'Ledger'[GL Account] = MAX ( 'Mapping'[GL Account] )
        ),
        'Ledger'[Values]
    )
RETURN
    IF (
        MAX ( 'Mapping'[Cost Classification] ) = "Costs",
        SUM ( 'Ledger'[Values] ) + _A,
        SUM ( 'Ledger'[Values] ) - _Other
    )

     Get the expected result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    Hi Anonymous ,

    According to your description, here's my solution.

    Create a measure.

    New Values =
VAR _A =
    SUMX (
        FILTER (
            ALL ( 'Ledger' ),
            'Ledger'[Cost Center] = "A"
                && 'Ledger'[GL Account] <> 3
        ),
        'Ledger'[Values]
    )
VAR _Other =
    SUMX (
        FILTER (
            ALL ( 'Ledger' ),
            'Ledger'[Cost Center] = "A"
                && 'Ledger'[GL Account] <> 3
                && 'Ledger'[GL Account] = MAX ( 'Mapping'[GL Account] )
        ),
        'Ledger'[Values]
    )
RETURN
    IF (
        MAX ( 'Mapping'[Cost Classification] ) = "Costs",
        SUM ( 'Ledger'[Values] ) + _A,
        SUM ( 'Ledger'[Values] ) - _Other
    )

     Get the expected result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.