cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Need to show sum of Sales after Grouping by Customer.

I have Date range as a slicer

 

When I select date I need to show Total Sales.

 

Data:

 

CustomerDateSales
A01-Jan100
A01-Feb200
A01-Mar300
A01-Apr400
B01-Jan10
B01-Feb20
B01-Mar30
B01-Apr40

 

 

If i Selected Date is between 01 Feb - 01 Apr

 

Required output:

 

CustomerDateSalesSum
A01-Feb900
A01-Mar900
A01-Apr900
B01-Feb90
B01-Mar90
B01-Apr90

 

If i Selected Date is between 01 Feb - 01 Mar

 

CustomerDateSalesSum
A01-Feb500
A01-Mar500
B01-Feb50
B01-Mar50

 

 

I tried implementing with use of ALLexcept but it is not considering date filter.

Request to please help.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Need to show sum of Sales after Grouping by Customer.

@Developer_2202 ,

 

You need to create another table 'Table (2)' with one date column as below and create the slicer based on the date column in the new table:

1.PNG  

 

Then create a measure using dax below:

Sales Total = CALCULATE(SUM('Table'[Sales]), FILTER(ALLEXCEPT('Table', 'Table'[Customer]), 'Table'[Date] in VALUES('Table (2)'[Date])))

2.PNG

 

You can also refer to the pbix.

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Highlighted
Super User IV
Super User IV

Re: Need to show sum of Sales after Grouping by Customer.

You ALLEXCEPT should look like ALLEXCEPT('Table','Table'[Customer]), does it?


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper I
Helper I

Re: Need to show sum of Sales after Grouping by Customer.

Thanks for reply Greg.

I am using 

CALCULATE(SUM('Table'[Sales]),ALLEXCEPT('Table','Table'[Customer]))
 
The value is not changing if we change the dateslicer. It is grouping up for each customer though.
Highlighted
Community Support
Community Support

Re: Need to show sum of Sales after Grouping by Customer.

@Developer_2202 ,

 

You need to create another table 'Table (2)' with one date column as below and create the slicer based on the date column in the new table:

1.PNG  

 

Then create a measure using dax below:

Sales Total = CALCULATE(SUM('Table'[Sales]), FILTER(ALLEXCEPT('Table', 'Table'[Customer]), 'Table'[Date] in VALUES('Table (2)'[Date])))

2.PNG

 

You can also refer to the pbix.

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Helper I
Helper I

Re: Need to show sum of Sales after Grouping by Customer.

Thank you very much! It worked as expected 🙂

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors