cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MaxViana
Frequent Visitor

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?


Graph and table.pngMeasures.png

5 REPLIES 5
philipe_ito
New Member

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

philipe_ito_0-1656958263318.png

 

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.

 

RicoZhou
Community Support
Community Support

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. 

 

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


RicoZhou
Community Support
Community Support

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)

1.png

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. 

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

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors