Calculate total Sales

[arobinson]

I have 3 total columns I am working from. Customer, Parent Customer, and amount billed.

I am looking to make a table or matrix that pulls the top 10 amount billed for the year. I want this list to be a mix of Customers and Parent Customers, as some of the Customers have Parent Customers and some don't. I want any Customer that have a parent customer to sum up into one name under the Parent Customer. If there is no Parent Customer it will stand alone on the list. 

[Anonymous]

What about creating a calc column called Effective Customer like this

Customer	Parent Customer	Effective Customer
Cust 1		Cust 1
Cust 2	Cust 1	Cust 1
Cust 3	Cust 2	Cust 2

and then slicting by the last column?

[Fowmy]

@arobinson 

Create a Hierarchy by dragging the CUSTOMER field on to the PARENT field. Watch this video if necessary (https://www.youtube.com/watch?v=1Y0T_KkeWyQ)

And, add the following measure

TOP 10 Customers = 

VAR _RANK = 
    RANKX(
      ALL(DATA[Customer]),
        CALCULATE(SUM(DATA[Amount Billed])),,
       DESC
    )
RETURN
IF(
    _RANK > 10,
    BLANK(),
    CALCULATE(SUM(DATA[Amount Billed]))
)

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[amitchandak]

@arobinson , not very clear.

You can top 10 like this

Top 10 City Rank = CALCULATE([Sales],TOPN(10,all(Geography[City]),[Sales],DESC),VALUES(Geography[City Id]))

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...

[Fowmy]

@arobinson 

Can you share some sample data and the expected result as data to have a clear understanding of your question?

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[arobinson]

Customer                                                        Parent Customer                                           Amount Billed

1	No Parent Customer	$1033
2	Parent 1	$5121
3	Parent 2	$5166
4	Parent 2	$76621
5	Parent 1	$1211
6	No Parent Customer	$5151
7	Parent 1	$8111
8	Parent 2	$51512
9	No Parent Customer	$6732
10	No Parent Customer	$4445