Forum Discussion

MaxViana's avatar
MaxViana
Frequent Visitor
5 years ago

Problem using Pareto: accumulated error on repeated values

Hello friends!

I need to create a Pareto chart and I'm finding it difficult to create a metric that shows me the cumulative total for each row ordered by a ranking.

I can create the ranking. However, my accumulated value measurement is in error when the line value is repeated.

How to solve this annoying problem?


Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi MaxViana 

    I think your problem is that when your Atual is repeated, you will get the wrong acc.

    Ex: Toc... Atual = 9, Acc should be 225+9 = 234 instead of 243.

         Tag... Atual = 9, Acc should be 234+9 = 243.

    Is it right?

    I think you can achieve your goal by build a ranking column(not a measure) which is not repeated.

    Try this measure:

    Acc = SUMX(FILTER(ALL('Ocorrencias'),'Ocorrencias'[Rank]<=MAX('Ocorrencias'[Rank])),'Ocorrencias'[Total])

    If this reply still couldn't help you solve your problem, please share a sample with me by your Onedrive for Business.

     

    Best Regards,

    Rico Zhou

     

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

     

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi MaxViana 

        I will use your sample in your new post to have a test, I think you are still confused in the same problem.

        Firstly in build a NameRank in Ocorrenicas table.

        NameRank = RANKX(Ocorrencias,Ocorrencias[Município Fato],,ASC,Dense)

        Then Build new rank measure.

        NameRanking = CALCULATE(MAX(Ocorrencias[NameRank]),ALLEXCEPT(Ocorrencias,Ocorrencias[Município Fato]))
        NewRanking = 
RANKX(ALL(Ocorrencias[Município Fato]),[Ranking]+[NameRanking]/1000,,ASC,Dense)

        Then you can calculate Acc and Acc%by your new rank.

        For more details: How to use RANKX in DAX (Part 2 of 3 – Calculated Measures)

         

        Best Regards,

        Rico Zhou

         

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

  • philipe_ito's avatar
    philipe_ito
    New Member
    4 years ago

    Olá MaxViana.

     

    Eu enfrentei um problema similar ao criar um pareto e não conseguia resolver com o rankX. No entanto consegui encontrar uma maneira de "resolver" este problema com uma pequena gambiarra. Para mim deu certo, não sei se seria a solução para você:

     1 - na sua base de dados, adicione uma coluna de index a partir de 1

     

    Feito isso, crie uma nova coluna somando o seu valor [Ocorrências] + o valor da coluna dividido por um valor bem alto, por exemplo 100.000.000 (use um número alto o suficiente para não atrapalhar os teus dados) , utilize esta nova coluna como refêrencia para seu cálculo de pareto.