Divide sum by total for items where sum is lower than zero

[lokosrio]

Hi,

I have a data like in the table below:

On my chart I want to aggregate them like in the table below:

1. I want to show only values where the sum < 0
2. Divide the sum/total to present the result as a contribution

3. Be able to filter by other columns

Right now I have only DAX which helps me get the elements where the value is bigger than zero:

CALCULATE(SUM('Table'[Measure]),
FILTER(ALL('Table'[Measure]),
SUM('Table'[Measure]) < 0))

Could you help me with this, please?

Thank you in advance!

[V-lianl-msft]

Hi @lokosrio ,

Try:

sum if<0 =
VAR sum_by_dim1 =
    CALCULATE (
        SUM ( 'Table (2)'[Measure] ),
        ALLEXCEPT ( 'Table (2)', 'Table (2)'[Dimension1] )
    )
RETURN
    IF ( sum_by_dim1 < 0, sum_by_dim1 )

result2 =
VAR total =
    CALCULATE (
        SUM ( 'Table (2)'[Measure] ),
        FILTER ( ALL ( 'Table (2)' ), [sum if<0] < 0 )
    )
RETURN
    DIVIDE ( [sum if<0], total )

Sample .pbix

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

[lokosrio]

@V-lianl-msft Unfortunately, I cannot apply filters in this solution.

[sturlaws · Accepted solution]

Hi @lokosrio 

could you try to write your measures like this:

Total =
VAR _tmp =
    SUMMARIZE (
        FILTER (
            ADDCOLUMNS (
                CALCULATETABLE ( VALUES ( 'Table'[Dimension 1] ), ALL ( 'Table'[Dimension 1] ) ),
                "t", CALCULATE ( SUM ( 'Table'[Amount] ) )
            ),
            [t] < 0
        ),
        'Table'[Dimension 1]
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER ( ALL ( 'Table'[Dimension 1] ), 'Table'[Dimension 1] IN _tmp )
    )

and

Contribution = divide(sum('Table'[Amount]),[Total])

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[lokosrio]

Hi @sturlaws,

works perfect! Thank you very much.

How can I add a column ( 'Table'[Dimension 2] ) in your code to apply drill down on a chart?

Regards

[sturlaws]

When you drill down, the contribution should sum up to 100% on that selection?

[lokosrio]

Yes, the contribution should sum up to 100%

[sturlaws]

You would have to compute for the other dimension as well, and use ISFILTERED to determine which total to use:

Total =
VAR _tmpDimension1 =
    SUMMARIZE (
        FILTER (
            ADDCOLUMNS (
                CALCULATETABLE ( VALUES ( 'Table'[Dimension 1] ), ALL ( 'Table'[Dimension 1] ) ),
                "t", CALCULATE ( SUM ( 'Table'[Amount] ) )
            ),
            [t] < 0
        ),
        'Table'[Dimension 1]
    )
VAR _tmpDimension2 =
    SUMMARIZE (
        FILTER (
            ADDCOLUMNS (
                CALCULATETABLE ( VALUES ( 'Table'[Filter 2] ), ALL ( 'Table'[Filter 2] ) ),
                "t", CALCULATE ( SUM ( 'Table'[Amount] ) )
            ),
            [t] < 0
        ),
        'Table'[Filter 2]
    )
RETURN
    IF (
        ISFILTERED ( 'Table'[Filter 2] ),
        CALCULATE (
            SUM ( 'Table'[Amount] ),
            FILTER ( ALL ( 'Table'[Filter 2] ), 'Table'[Filter 2] IN _tmpDimension2 )
        ),
        CALCULATE (
            SUM ( 'Table'[Amount] ),
            FILTER ( ALL ( 'Table'[Dimension 1] ), 'Table'[Dimension 1] IN _tmpDimension1 )
        )
    )

[lokosrio]

@sturlaws 

Works perfectly.

Thank you very much!

[amitchandak]

@lokosrio , Try like

divide(if(SUM('Table'[Measure])<0,SUM('Table'[Measure]),blank()),calculate(SUM('Table'[Measure]), all('Table')))

[lokosrio]

Unfortunately the result is not ok: