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.
Following is the sample data used in the report to find the Top customers who are contributing to the 80% of the total amount
Year | Month | Customer | Amount INR | Amount USD |
2017 | Feb | Customer 1 | 6500 | 108 |
2017 | Feb | Customer 3 | 5000 | 83 |
2017 | Jan | Customer 3 | 4500 | 75 |
2017 | Jan | Customer 2 | 3000 | 50 |
2017 | Feb | Customer 2 | 2500 | 42 |
2017 | Mar | Customer 1 | 2500 | 42 |
2017 | Feb | Customer 4 | 2300 | 38 |
2017 | Apr | Customer 2 | 2300 | 38 |
2017 | Apr | Customer 1 | 2200 | 37 |
2017 | Jan | Customer 1 | 2000 | 33 |
100% Amount | 32800 | 547 | ||
80% Amount | 26240 | 437 |
Would like to find find out who are those customers who will full fill the 80% of the total amount in the desc order for both INR and USD
Report consists of calender , currency type and customers filter
any solution on this will be very helpfull
Thanks in advance
Solved! Go to Solution.
Hi @Cgowdar
(With your sample date)
I hope this will help although your original model would be complex
Here is what I have Done
1) RANK the Customers based on AMOUNT (lets say INR)
RANK INR = IF ( HASONEVALUE ( TableName[Customer] ), RANKX ( ALLSELECTED ( TableName[Customer] ), CALCULATE ( SUM ( TableName[Amount INR] ) ), , DESC, DENSE ) )
2) Add Cumulative Totals using RANK from 1st to last Ranked Customer
CumulativeTotals = VAR CurrentRANK = [RANK INR] RETURN SUMX ( FILTER ( ALLSELECTED ( TableName[Customer] ), CALCULATE ( [RANK INR] ) <= CurrentRANK ), CALCULATE ( SUM ( TableName[Amount INR] ) ) )
3) Last step..Cumulative contribution by each Customer based in order of their RANK
Cumulative Percentage = [CumulativeTotals] / CALCULATE ( SUM ( TableName[Amount INR] ), ALLSELECTED ( TableName[Customer] ) )
Hi @Cgowdar
(With your sample date)
I hope this will help although your original model would be complex
Hi
Is there a way to filter by top x%, but default setting should be to show top 80%. Filter should allow 10-100% and in 10% increments?
Thanks
Simona
@Zubair_Muhammad wrote:Hi @Cgowdar
(With your sample date)
I hope this will help although your original model would be complex
@Zubair_Muhammad wrote:Hi @Cgowdar
(With your sample date)
I hope this will help although your original model would be complex
Here is what I have Done
1) RANK the Customers based on AMOUNT (lets say INR)
RANK INR = IF ( HASONEVALUE ( TableName[Customer] ), RANKX ( ALLSELECTED ( TableName[Customer] ), CALCULATE ( SUM ( TableName[Amount INR] ) ), , DESC, DENSE ) )
2) Add Cumulative Totals using RANK from 1st to last Ranked Customer
CumulativeTotals = VAR CurrentRANK = [RANK INR] RETURN SUMX ( FILTER ( ALLSELECTED ( TableName[Customer] ), CALCULATE ( [RANK INR] ) <= CurrentRANK ), CALCULATE ( SUM ( TableName[Amount INR] ) ) )
3) Last step..Cumulative contribution by each Customer based in order of their RANK
Cumulative Percentage = [CumulativeTotals] / CALCULATE ( SUM ( TableName[Amount INR] ), ALLSELECTED ( TableName[Customer] ) )
Hi @Zubair_Muhammad, I have calculated the cumulated %. How do I calculate the count of customers contributing to 80% of value.
I have used this Dax
@Zubair_Muhammad
please share pbix file, its not found in one drive, I am looking on same requirement
This worked like a Pro.
Thanks a lot for your valuable reply.
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |