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
APini
New Member

Cumulative Sales

I want to write a measure which filters the data by multiple columns.

 

I want a cumulative total for a product over the course of twelve weeks and then I want to have a measure which shows the performance of an individual sales person to match against the performance of the whole team.

 

Week NumberProduct Value Sales Person
1Apples £                       10Annie
1Apples £                       10Jon
1Apples £                       10Tom
1Apples £                       10Jon
1Apples £                       10Annie
1Apples £                       10Jon
1Apples £                       10Dave
1Apples £                       10Tom
1Apples £                       10Jon
1Apples £                       10Annie
2Apples £                       10Dave
2Apples £                       10Annie
2Apples £                       10Tom

 

I use the following dax formula to get the cumulative total

 

= CALCULATE(SUM('table'[value]) , FILTER(ALL('table'),table[week number]<=MAX('table'[week number]), VALUE('table'[Product]))

 

what do I need to add to just get the cumulative sales for Dave?

 

 

 

1 ACCEPTED SOLUTION
vitinho
Helper I
Helper I

Hi,

Did you try?
= CALCULATE(SUM('table'[value]) ,
                       FILTER(ALL('table'),
                                  AND(table[week number]<=MAX('table'[week number]),
                                          table[Sales Person] = "Dave"),
                       VALUE('table'[Product]))


View solution in original post

2 REPLIES 2
vitinho
Helper I
Helper I

Hi,

Did you try?
= CALCULATE(SUM('table'[value]) ,
                       FILTER(ALL('table'),
                                  AND(table[week number]<=MAX('table'[week number]),
                                          table[Sales Person] = "Dave"),
                       VALUE('table'[Product]))


That's great thanks it's worked. 

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.