Calculated value using Pareto principle

[Anonymous]

I would like to use the pareto principle to calculate how many users amount for a certain precentage of a total sum and I can't seem to get it working. I've vaccumed the forum and found many articles about pareto, but always for diagrams and such. I just want one number calculated. I want to know how many accounts amounts for 50% of the total sales.

Imagine it's sales data like this.

Account	Sales
User 16	19 000
User 2	17 100
User 9	13 600
User 7	12 000
User 4	10 000
User 8	7 000
User 19	6 900
User 14	4 500
User 18	4 000
User 1	3 500

Now in Excel I would sort it by biggest value, add a precentage per line and then add a final column giving me a running total, like this:

Account	Sales	%	Acc
User 16	19 000	19%	19%
User 2	17 100	18%	37%
User 9	13 600	14%	51%
User 7	12 000	12%	63%
User 4	10 000	10%	73%
User 8	7 000	7%	81%
User 19	6 900	7%	88%
User 14	4 500	5%	92%
User 18	4 000	4%	96%
User 1	3 500	4%	100%

Finally, I would just count the rows where Acc is below 50% + 1. This way, I would get that 3 our of my 10 accounts amounts for 50% of all the sales.

In my actual dataset, I have multiple columns that I want to do the same calculation for and then I want to be able to distribute the measure by department. I have another table with all the accounts and what department they belong to.

My end produkt should be something like this.

Department	Accounts	50% user	50% %
Department 1	15	3	20%
Department 2	20	10	50%
Department 3	10	4	40%

Thanks in advance!

[Anonymous · Accepted solution]

Hi Ville,

Firstly you would need a ranking column. The simplest would be to do this in your query editor since it is a built in function (Add column/Index column). Rank your Sales column in descending order when doing this.

Create a measure

mCumulativePerc = 

VAR vTotal = 
    CALCULATE(
        SUM('Table'[Sales]),
        ALLSELECTED('Table')
    )

VAR vCumulativeSum =
    CALCULATE(
        SUM('Table'[Sales]),
        FILTER(
            ALLSELECTED('Sales'),
            'Table'[Index] <= MAX('Table'[Index])
        )
    )

VAR vCumulativePerc =
    DIVIDE(
        vCumulativeSum,
        vTotal
    )

RETURN
    vCumulativePerc

And call it in a calculated column

Cumulative Percentage = [mCumulativePerc]

As for only looking at a certain range, you can use a slicer and use the calculated column as your field.

Hope you found this useful

Regards,

AndreM