cancel
Showing results for
Did you mean:
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

 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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User I

Hi @Cgowdar

See the attached file

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

Regards,
Zubair

Highlighted
Super User I

@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

4 REPLIES 4
Highlighted
Super User I

Hi @Cgowdar

See the attached file

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

Regards,
Zubair

Highlighted
Super User I

@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

Highlighted
Frequent Visitor

This worked like a Pro.

Highlighted
Helper II

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

Hi @Cgowdar

See the attached file

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

Hi @Cgowdar

See the attached file

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

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### 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