RANKX and cumulative sum not working properly.

[gabnarmor]

Hi, 

I`ve been trying to do a cumulative sum to make a pareto chart. I`ve tried multiple approaches that at first worked fine, but when combined with some filters I start to get some problems. The formula that has given the best results is 

Cumulative = 
CALCULATE([Spend USD], 
           TOPN([Ranking Supplier], 
                ALLSELECTED(f_net_source_saving_invoice[supplier_level_1_parent_name]), 
                [Spend USD], 
                DESC))

Where the rank is defined as 

Ranking Supplier = 
   RANKX(ALLSELECTED(f_net_source_saving_invoice[supplier_level_1_parent_name]),
    [Spend USD], , DESC)

But when using the filters I get the next result

Supplier	Spend Amount	Ranking Supplier	Cumulative	% Accumulated Spend
A	317	1	317	0.10
B	235	3	552	0.18
C	214	3	776	0.25
D	226	4	10005	0.32
E	200	6	215	0.07
F	181	6	1496	0.48

I would apreciate any posible solution someone could come up with 

[Anonymous · Accepted solution]

Hi @gabnarmor ,

Based on the information you provided, you can follow these steps:

1. Add new measure.

SORT =

RANKX (

    FILTER ( ALLSELECTED ( 'Table' ), 'Table'[% Accumulated Spend] ),

    CALCULATE ( MAX ( 'Table'[% Accumulated Spend] ) ),

    ,

    ASC

)

Cumulative =

CALCULATE (

    SUM ( 'Table'[Spend Amount] ),

    FILTER (

        ALL ( 'Table' ),

        'Table'[% Accumulated Spend] <= SELECTEDVALUE ( 'Table'[% Accumulated Spend] )

    )

)

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

^{Best Regards,}

^{Ada Wang}

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

[gabnarmor]

It worked well. Thank you very much.

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.

[gabnarmor]

The data I have is the following

Supplier	Spend Amount
A	317
B	235
C	214
D	226
E	200
F	181

The desairable result would be 

Supplier	Spend Amount	% Accumulated Spend	Ranking Supplier	Cumulative
A	317	0.23	1	317
B	235	0.40	2	552
D	226	0.57	3	778
C	214	0.72	4	992
E	200	0.87	5	1192
F	181	1	6	1373

Please take into account that I am working with a lot of filters that users can choose to use or not. So the issue probably can`t be replicated easily even with the data