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 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!
 
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])

     

6 Replies

  • 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
  • v-diye-msft's avatar
    v-diye-msft
    Community Support
    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])