Last Week - Subtotals of filters displaying grand totals

[Duckling68]

I'm sorry to have to post, I've been trying to work this out myself all weekend through this site, YouTube and Dr Google, and am gradually feeling the life sap out of me! Thanks in advance to anyone who is able to help.

I have a table of weekly retailer sales data (TableWeekly Sales), and within that table there is column that pulls data from another table (TableVisits) which tells me if a member of our team has been to that store. I also have a separate 'Calendar' table, which links them all together with the column 'Week Commencing'. Here is the structure:

To validate the impact, I want to see the sales in all the stores we visited (and, conversely, those we didn't visit) in both the week we visited and the week prior.

The universe of stores is around 4000 outlets across 5 retailers, and those visited changes week by week so it's important the the previous week's sales is the total for the stores that we have visited in the current week. 

Here's where I'm at. My best pass so far is with this formula:

NSV Last Week =

CALCULATE (

SUMX('TableWeekly Sales','TableWeekly Sales'[NSV]),

FILTER (

ALL('TableWeekly Sales'),

COUNTROWS (

FILTER (

'TableWeekly Sales',

EARLIER ('TableWeekly Sales'[Week Commencing] ) = DATEADD ( 'TableWeekly Sales'[Week Commencing], -7, DAY )

))))

This is the matrix it returns:

As you can see, it's happily returning the last week NSV (hurrah!), however, it returns the total value for both the 'visited' and 'not visited' stores! What I want to see is the totals of 'Visited' and 'Not Visited' adding up to the grand total. However, the subtotals for 'visited' and 'not visited' should never match week on week, because the 'visited' universe is constantly changing - for example, we may visit 1000 stores one week and only 800 the next.

Below are some other outputs I've had in my trials:

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

Using this formula:

NSV Last Week 2 =

VAR CurrentWeek = SELECTEDVALUE('Calendar'[Week Number] )

VAR CurrentYear = SELECTEDVALUE('Calendar'[Year] )

VAR MaxWeekNumber = CALCULATE( MAX( 'Calendar'[Week Number] ) , ALL('Calendar' ) )

RETURN

SUMX(

FILTER (ALL ('Calendar'),

IF(CurrentWeek = 1,

'Calendar'[Week Number] = MaxWeekNumber && 'Calendar'[Year] = CurrentYear - 1,

'Calendar'[Week Number] = CurrentWeek - 1 && 'Calendar'[Year] = CurrentYear )),

SUM('TableWeekly Sales'[NSV]) )

I have this matrix output (and seriously I have no idea where those massive numbers are coming from but it's obviously not right!)

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

And this formula:

NSV Last Week 2 =

VAR CurrentYear =

IF ( HASONEVALUE ( 'Calendar'[Year] ), VALUES ( 'Calendar'[Year] ) )

VAR CurrentWeek =

IF (

HASONEVALUE ( 'Calendar'[Week Number] ),

VALUES ( 'Calendar'[Week Number] )

)

VAR NSVLY =

CALCULATE (

MAX ( 'Calendar'[Week Number] ),

ALL ( 'Calendar' ),

'Calendar'[Year] = CurrentYear - 1

)

VAR Result =

IF (

CurrentWeek = 1,

CALCULATE (

SUM('TableWeekly Sales'[NSV] ),

ALL ( 'Calendar' ),

'Calendar'[Week Number] = CurrentWeek - 1,

'Calendar'[Year] = CurrentYear

)

)

RETURN

Result

Simply returns nothing!

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

So, to summarise, I'm super frustrated and really hoping someone is able to guide me in the right direction. If you're anywhere near Nottingham, UK, there's even a cream cake in it for you! 

Once again, thank you in advance.

[parry2k]

@Duckling68 at a high level, is this what you are looking for?

[Duckling68]

Thank you @parry2k , but I'm afraid not. I could get to this but it doesn't meet the reporting I need. I'm finding it really hard to explain but each week, for the 'visited' and 'not visited' outlets I want the report to output 'this week' and 'last week' based on whether the stores were visited or not in 'this week' (and so ignore whether they were 'visited' or 'not visited' last week). So I would not expect to ever see an exact match:

I have pulled some of the raw data into an Excel file. You can see here, that Asda4196 was visited in w/c 21/06 but not visited in w/c 28/06, whereas Asda4220 was visited in both 21/06 and also 28/06 - so in my reporting store Asda4196 would be included as 'visited' in w/c 21/06 but 'not visited' in w/c 28/06. Conversely, Asda 4220 would be included as 'visited' in the outputs for both w/c 21/06 and w/c 28/06.

To get the outputs I need in Excel, I need to pull into 2 tables as shown below.

The Excel file is here: https://tinyurl.com/z6nrp53j

I hope this explains my quandary better. Thanks once again for trying to help.

[Duckling68]

Now using this:

I have managed to get the totals for 'visited' and 'not visited' to display values for 'NSV' which add up to the "correct" total for the line and work if I apply slicers by retailer.

I just now need to sort that 'NSV Last Week' returns the value which equates to the selection in 'Visited' this week (as they will be different to the ones visited last week). 

Thanks again for looking at this for me @parry2k .

[v-kkf-msft · Accepted solution]

Hi @Duckling68 ,

Try the following formula:

NSV Last Week = 
var tab = 
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Week Commencing] = MAX('Calendar'[Week Commencing]) - 7
    )
return 
    CALCULATE(
        SUM('Table'[NSV]),
        INTERSECT( 
            CALCULATETABLE( VALUES('Table'[Store]), tab ), 
            VALUES('Visited'[Store]) 
        ),
        tab
    )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[Duckling68]

@v-kkf-msft - thank you SO much! That has completely solved it for me, I can't tell you how grateful I am.  Really appreciated.

Have a good weekend

Cath

[parry2k]

@Duckling68 you are over-engineering the solution, it will be easier if you can share the pbix file with the expected output and will get back to you with the solution.

[Duckling68]

@parry2k - thank you.

I'm pleased to hear I'm overengineering! There is so much opinion out there, I've been down some real rabbit holes.

Here's the link to the pbix: 

https://tinyurl.com/u7jpjh96

I would expect 'NSV Last Week' for 'Visited'/'Not Visited' to add up to the 'Total' column, and the values to be in the region of the 'NSV' values, although not exact - ideally my story would tell, say 5% less than 'NSV' in 'Visited', and 5% more in 'Not Visited'.

Hopefully this explains what I'm expecting.

Thank you again for helping.