Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculated value using Pareto principle

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.

AccountSales
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:

AccountSales%Acc
User 16       19 00019%19%
User 2       17 10018%37%
User 9       13 60014%51%
User 7       12 00012%63%
User 4       10 00010%73%
User 8         7 0007%81%
User 19         6 9007%88%
User 14         4 5005%92%
User 18         4 0004%96%
User 1         3 5004%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.

DepartmentAccounts50% user50% %
Department 115320%
Department 2201050%
Department 310440%

 

Thanks in advance!

  • Anonymous's avatar
    Anonymous
    6 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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