Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Accumulate Negative Values

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 hi...
Need Help
  • v-diye-msft's avatar
    v-diye-msft
    6 years ago

    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's avatar
v-diye-msft
Community Support
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago
    Try this:
    Measure =
    VAR LastVisible = MAX ( TABLE[Rank] )
    RETURN CALCULATE (
    SUM(VALUES),
    TABLE[RANK] <= LastMonthVisible
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

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

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

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