Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
46 | |
31 | |
30 | |
18 | |
18 |