Accumulate Negative Values

[Anonymous]

Hi all !!

I need to make a pareto chart but with negative values, for this I need to accumulate the negative values from higher to lower.

The first step is to rank the negative totals from highest to lowest:

Rank Negative = RANKX(ALL(D_Stores[id_store]);[Negative Total])

The next step is to accumulate all the negative values from highest to lowest and this is where I have the problem, what do you recommend I do?

If they were positive values it would be as follows:

Pareto Value =  SUMX(TOPN([Rank Positive];ALL(D_Stores[id_store]);[Total Positive]);[Total Positive])

This case doesn't work for negative values.

Thanks!

Regards!

[v-diye-msft · Accepted solution]

Hi @Anonymous 

Thanks for your feedback.

Based on your details, I modified my sample and the measures:

Use below measure to calculate the rankx :

Rankx = RANKX(ALL('Sample'),[ValuesMeasure],,DESC)

Then use the below measure to generate the cumulative results:

FinalResults = var a = [Rankx]
Return 
SUMX(FILTER(ALL('Sample'),[Rankx]<=a),[ValuesMeasure])

[v-diye-msft]

Hi @Anonymous 

Please let me know if you'd like to get below results:

1. simple table below:

2. Add rank column:

Rank = RANKX('Sample',[Values],,DESC)

3. Add the measure:

Measure 4 = CALCULATE(SUM('Sample'[Values]),FILTER(ALL('Sample'),[Rank]<=MAX('Sample'[Rank])))

[Anonymous]

Hi @v-diye-msft !

Thank you for your reply ! In this case it doesn't work.

The problem is this:
The columns "Values Negative" and "Rank" are two measures, so I couldn't use the last MAX of your measure.

Values Negatives = CALCULATE ( [TOTAL]; FILTER ( D_Store; [TOTAL] < 0 ) )

Rank = RANKX(ALL(D_Store[store id]);[Values Negatives];;ASC)

What I need is to create a measure that accumulates the negative values from ranking 1 to the last.

For example the "New Measure":

Regards!

[Anonymous]

Try this:

Measure =

VAR LastVisible = MAX ( TABLE[Rank] )

RETURN CALCULATE (

SUM(VALUES),

TABLE[RANK] <= LastMonthVisible

[Anonymous]

Hi @Anonymous is not possible because rank is a measure, it is not stored in a table.

[Anonymous]

Instead of a measure, you can create a calculated column for the same.