Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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 ) )
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?
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
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 ) )
Thanks...I was very close
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.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |