Top customers who are contributing to the 80% of the total amount

[Cgowdar]

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

[Zubair_Muhammad · Accepted solution]

Hi @Cgowdar

See the attached file

(With your sample date)

I hope this will help although your original model would be complex

[Zubair_Muhammad · Accepted solution]

Hi @Cgowdar

See the attached file

(With your sample date)

I hope this will help although your original model would be complex

[a-simarg]

 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

See the attached file

(With your sample date)

I hope this will help although your original model would be complex

---

---

@Zubair_Muhammad wrote:

Hi @Cgowdar

See the attached file

(With your sample date)

I hope this will help although your original model would be complex

---

[Zubair_Muhammad · Accepted solution]

@Cgowdar

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] ) )

[B_Tender]

I signed in to give you an upvote. 

[jubinjct10]

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

80% = COUNTROWS(FILTER(VALUES('Table'[Customer Name]),[Cumulative Percentage]<=0.8)).

You help is highly appreciated.

[RENJITH_R_S]

@Zubair_Muhammad 

please share pbix file, its not found in one drive, I am looking on same requirement

[Cgowdar]

This worked like a Pro.

Thanks a lot for your valuable reply.