Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
v-yuta-msft
Community Support
Community Support

@Anonymous ,

 

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
v-yuta-msft
Community Support
Community Support

@Anonymous ,

 

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.

Anonymous
Not applicable

Thank you very much! It worked as expected 🙂

Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.