Pareto Chart with a Percentage Measure Power BI

Question

I'm having an issue with a dashboard that I'm creating.

I need to make a Pareto Chart and I found a lot of tutorials on the Internet of how to make it in Power BI, however, I need to use a measure that is a percentage and this is where I'm stuck.

Here's a part of my table:

Database

I made a measure that is a percentage of Não_Recebido_Dinâmico by Fat_Liq1. The measure is:

% Inadimplência_Dinâmico = DIVIDE(SUM('Mapa_de_Faturamento (2)'[Não_Recebido_Dinâmico]);
                                  SUM('Mapa_de_Faturamento (2)'[Fat_Liq.1])) + 0

So I need to make a Pareto Chart with the top 10 Cursos by this measure. When I apply the ways of the Pareto Chart on the Internet it doesn't work because they use the total of the measure to make the accumulated percentage.

For example, these are the top 10 Cursos by the measure % Inadimplência_Dinâmico. I think that to make the Pareto Chart works properly, the total and the accumulated should be the sum of the measure, but that's not what happens in Power BI because it keeps considering it as a percentage.

Table

I've tried to make the same measure as a calculated column but it doesn't work either, because in this case, it sums up the percentage of all rows.

I'm not familiar with the DAX functions of Power BI, so I need some help.

This is what I want in Power BI, but made in Excel:

Example of what I want

Thank you all!

Alexis Olson Alexis Olson · Accepted Answer · 2018-08-07T21:47:06

First, let's create a calculated column for the ranking (names abbreviated for legibility):

Ranking = RANKX(
              SUMMARIZE('Mapa_'; 'Mapa_'[Curso]);
              CALCULATE(
                  DIVIDE(SUM('Mapa_'[Não_]); SUM('Mapa_'[Fat_]));
                  ALL('Mapa_'); 
                  'Mapa_'[Curso] = EARLIER('Mapa_'[Curso])))

Now we can create a cumulative measure:

Accum = DIVIDE(
            CALCULATE(
                SUM('Mapa_'[Não_]);
                FILTER(ALLSELECTED('Mapa_');
                    'Mapa_'[Ranking] <= MAX('Mapa_'[Ranking])));
            SUM('Mapa_'[Fat_]))

Now you can create a Line and Bar Chart with Curso on the shared axis, % Inadimplência_Dinâmico on the column values, and Accum on the line values.

Note that this will have all of the cursos until you apply appropriate filtering. To do this, go to the visual level filters (or page level or report level) and choose Top N filtering for Curso. You want to show the Bottom 10 item using the Ranking column as for the By value choice.

The ALLSELECTED part of the Accum measure will make sure you're only including the top 10 that you want and not all of the rows.

Pareto Chart with a Percentage Measure Power BI

1 Answers