Problem using Pareto: accumulated error on repeated values

[MaxViana]

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?

[philipe_ito]

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.

[v-rzhou-msft]

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. 

[MaxViana]

I was not able to do that, my friend RicoZhou. 😭
I don't know how to create a ranking in a column.

I've been struggling with this for a while. I'm almost giving up on making this Pareto.

Would you help me with it?
Sample:
https://1drv.ms/u/s!AoIveuLXWhhKgvBs0Sp-a2NhLHAwSg?e=KFgAEF

[v-rzhou-msft]

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. 

[MaxViana]

Realize that the problem persists. In the 13th and 14th rank, the cumulative measures still do not work.