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
andy1884
Frequent Visitor

week vs week for a sales person

Hi

 

I'm new to Power BI and have got sof far in replicating an existing report created in Cognos.

 

What I need is a last week sales value for each sales area.

 

My last week sales measure is saleslfl = calculate([sales],filter(all('basket summary'),'basket summary'[Week Num]=selectedvalue('basket summary'[Week Num])-1))

and this, as expected does retuen the correct sales value for last week, but as a total on each line.

 

How can I modify the measure so that is brings back the sales for last week for Region 1, Region 2 etc on each row.

The Region is coming from another table, StoreID which has the Region on it. Both tables are joined on StoreID

1 ACCEPTED SOLUTION
Stachu
Community Champion
Community Champion

ok, clear now, can you try this

saleslfl =
CALCULATE (
    [sales],
    FILTER (
        ALL ( 'basket summary'[Week Num] ),
        'basket summary'[Week Num]
            = SELECTEDVALUE ( 'basket summary'[Week Num] ) - 1
    )
)


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

12 REPLIES 12
Stachu
Community Champion
Community Champion

from what I see your original measure will work fine if you just put Region in rows of your visual, you just may need to switch to Matrix instead of Table visual, and drill down appropriately - or is this approach giving you wrong resutls?
can you share the view with Regions granularity visible?



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Regions are on the original, but as they contain people's names, I left them on the picture.

 

I've replaced them here with codes (and that has made the rank of other measure all go to 1 - but I know how to deal with that).

The column on the right is the correct number (as a total) but is the same on each line. 

I need to see the sales for SalesArea 11, 12 , 13 etc

 

Capture.JPG

Stachu
Community Champion
Community Champion

ok, clear now, can you try this

saleslfl =
CALCULATE (
    [sales],
    FILTER (
        ALL ( 'basket summary'[Week Num] ),
        'basket summary'[Week Num]
            = SELECTEDVALUE ( 'basket summary'[Week Num] ) - 1
    )
)


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Thanks...I was very close

Soulus101
New Member

Hi Andy,

 

Have you tried storing the measure against StoreID table? The measure should then look like this:

 

salesfl:=CALCULATE(SUM(RELATED([sales])),filter(all('basket summary'),'basket summary'[Week Num]=selectedvalue('basket summary'[Week Num])-1))

That gives me an error - The  SUM function only accepts a column reference as an argument

Apologies, I'm making assumptions about your model here... is [Sales] a measure? Could you describe it?

 

yes, [sales] is a measure defined as sales = sum('basket summary'[GrossValue])

Try this:

 

CALCULATE(SUM(RELATED('basket summary'[GrossValue])),filter(all('basket summary'),'basket summary'[Week Num]=selectedvalue('basket summary'[Week Num])-1))

still the same error message, I'm afraid 

Apologies, looks like it's more complicated than I first thought!

 

I'll have a think and let you know if I can figure it out.

andy1884
Frequent Visitor

Here is what it looks like at the moment, with a filter on Week. All working fine apart from the saleslfl column with is a total of the previous week

Capture.JPG

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.