Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Problem with Pareto

 

Hey guys - I kindly ask for your advice.

These two charts are my pareto calculation: 

pareto = var total = calculate(COUNT('Stammdaten Komplett'[ID] ); ALLSELECTED('Stammdaten Komplett'))
var currentnumber = count('Stammdaten Komplett'[ID])
var summarizetable= SUMMARIZE(ALLSELECTED('Stammdaten Komplett');'Stammdaten Komplett'[SAPPartNumber]; "Revenue"; COUNT('Stammdaten Komplett'[ID]))
var cumulativesum = SUMX(filter(summarizetable; currentnumber <=[Revenue]);[revenue])
return cumulativesum/total*100

The second right chart is correct but the first left one is not. Yes the pareto line overcomes 80 but to late. He just jumps. If I calculate the datas in Excel my pareto looks different and is correct. I use Databases and can not take Excel as a source. 

Thank you for your help!


 
  • Anonymous's avatar
    Anonymous
    6 years ago
    I don't quite get the code you've written but I'll tell you what you have to do. For each and every item on your x-axis you have to calculate the cumulative sum of all the items that precede the item in terms of the measure and then find all the items that have exactly the same value of the measure as the current item. Then among all the items with the same value you have to only sum up those that precede the current item plus the current item. This time the order will be not by the measure value (as they all have the same value) but by the order of the items (I guess it'll be alphabetical). This is how you properly build the pareto line.

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    This, of course, happens because when calculating the cumulatives you have to take into consideration not only the values of the measures of the items but also the order of the items when their measures have the same values. From the point of view of the measure some of your SAP Part Numbers are INDISTINGUISHABLE, so the values for them get summed up. You have to, so to speak, break the ties (as some here have already indicated).
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you for the reply. Could you give me a idea how to distinguish the data?

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        I remember that I have seen a fully codede solution to this kind of problem on this forum. Please use the search facility to find the post by the user 'darlove'.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      RANX pareto =
      RANKX(
      'Stammdaten Komplett';
      'Stammdaten Komplett'[pareto SAP]
      ;;DESC
      )
       



      I am sorry - I do not understand.
      or must I integret in this measure: 

      pareto SAP = var total = calculate(COUNT('Stammdaten Komplett'[ID] ); ALLSELECTED('Stammdaten Komplett'))
      var currentnumber = count('Stammdaten Komplett'[ID])
      var summarizetable= SUMMARIZE(ALLSELECTED('Stammdaten Komplett');'Stammdaten Komplett'[SAPPartNumber]; "Revenue"; COUNT('Stammdaten Komplett'[ID]))
      var cumulativesum = SUMX(filter(summarizetable; currentnumber <=[Revenue]);[revenue])
      return cumulativesum/total*100