topic Re: Calculate total Sales in DAX Commands and Tips

Calculate total Sales

arobinson — Tue, 28 Jul 2020 15:17:11 GMT

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.

Re: Calculate total Sales

Fowmy — Tue, 28 Jul 2020 15:21:12 GMT

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

Re: Calculate total Sales

arobinson — Tue, 28 Jul 2020 15:26:09 GMT

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

Re: Calculate total Sales

amitchandak — Tue, 28 Jul 2020 15:41:42 GMT

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/367415

Re: Calculate total Sales

Fowmy — Tue, 28 Jul 2020 16:08:46 GMT

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

Re: Calculate total Sales

Anonymous — Wed, 29 Jul 2020 15:30:09 GMT

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?