Pareto measure do work correctly when adding a slicer by year

[Anonymous]

I am using the below  measure to create a pareto chart. If I dont select the years in the slicer it seems to be working correctly.

However when I select a year is not working correctly,

Measure:

Pareto % =

VAR _total = CALCULATE(

    [Total Cost]

    , ALLSELECTED('in'[Function L5])

)

VAR _current = [Total Cost]

VAR _sumTable = SUMMARIZE(

    ALLSELECTED('in')

    , 'in'[Function L5]

    , "Cost", [Total Cost]

)

VAR _cummulativeSum = SUMX(

    FILTER(_sumTable, [Total Cost]>= _current)

    ,[Total Cost]

)

RETURN

DIVIDE(_cummulativeSum, _total)

Can someone help me figure out what Im missing here!

Thanks,

Laura

[Anonymous · Accepted solution]

Thanks for the reply from DataNinja777 , please allow me to provide another insight: 
Hi  @Anonymous ,

I created some data:

Here are the steps you can follow：

1. Create measure.

Pareto % =
VAR _total = CALCULATE(
    [Total Cost]
    , ALLSELECTED('in'[Function L5])
)
VAR _sumTable = SUMMARIZE(
    ALLSELECTED('in')
    , 'in'[Function L5]
    , "Cost", [Total Cost]
)
VAR _sumTable2 =
ADDCOLUMNS(
    _sumTable,"Sum_Cummulativesum",SUMX(FILTER(_sumTable,[Cost]>=EARLIER([Cost])),[Total Cost]))
var _cummulativeSum=
SUMX(
    FILTER(_sumTable2,[Function L5]=MAX('in'[Function L5])),[Sum_Cummulativesum])
RETURN
DIVIDE(
   _cummulativeSum,_total)

2. Result:

If the results above don't meet your expectations, Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Liu Yang

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

[DataNinja777]

Hi @Anonymous ,

Please try the following dax formula which respect any slicers applied to the data, including year slicers. This ensures that the calculations are only done within the context of the selected year(s) or any other slicers.

Pareto % =
VAR _total = CALCULATE(
    SUM('in'[Total Cost]),
    ALLSELECTED('in')
)
VAR _current = [Total Cost]
VAR _sumTable = 
    SUMMARIZE(
        ALLSELECTED('in'),
        'in'[Function L5],
        "Cost", SUM('in'[Total Cost])
    )
VAR _cumulativeSum = 
    SUMX(
        FILTER(_sumTable, [Cost] >= _current),
        [Cost]
    )
RETURN
DIVIDE(_cumulativeSum, _total)

Best regards,

[Anonymous]

Hi @DataNinja777 ,

I tried it and still doing the same.

[DataNinja777]

Hi @Anonymous ,

I tried with the dummy data and it is producing your required output in both before and after Pareto% measures.  

I have attached an example pbix file.  

Best regards,