cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

Following is the sample data used in the report to find the Top customers who are contributing to the 80% of the total amount

 

YearMonthCustomerAmount INRAmount USD
2017FebCustomer 16500108
2017FebCustomer 3500083
2017JanCustomer 3450075
2017JanCustomer 2300050
2017FebCustomer 2250042
2017MarCustomer 1250042
2017FebCustomer 4230038
2017AprCustomer 2230038
2017AprCustomer 1220037
2017JanCustomer 1200033
  100% Amount32800547
  80% Amount26240437

 

 

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

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User I
Super User I

Hi @Cgowdar

 

See the attached file

(With your sample date)

 

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

 

111.png

 

 

 

 

Regards,
Zubair


View solution in original post

Highlighted

@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] ) )
Regards,
Zubair


View solution in original post

4 REPLIES 4
Highlighted
Super User I
Super User I

Hi @Cgowdar

 

See the attached file

(With your sample date)

 

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

 

111.png

 

 

 

 

Regards,
Zubair


View solution in original post

Highlighted

@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] ) )
Regards,
Zubair


View solution in original post

Highlighted

This worked like a Pro.

 

Thanks a lot for your valuable reply.

 

Highlighted

 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

 

111.png

 

 

 

 



@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

 

111.png

 

 

 

 


 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors