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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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