cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
APini Frequent Visitor
Frequent Visitor

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

Accepted Solutions
vitinho Regular Visitor
Regular Visitor

Re: Cumulative Sales

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 Regular Visitor
Regular Visitor

Re: Cumulative Sales

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

APini Frequent Visitor
Frequent Visitor

Re: Cumulative Sales

That's great thanks it's worked. 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors