Weekly variations in table and total

[Jo_Chrq]

Hi all,

I'm having trouble creating a table in Power BI Desktop.

I have weekly sales for several stores and I would like to have a table (or matrix) with the weekly evolution.

Here's what I did :

Weekly Variation = 
VAR __PREV_WEEK = 
CALCULATE(
    SUM('table'[sales]),
    FILTER(
        ALL('table'), 'table'[week number] = MAX('table'[week number])-1
        )
)
VAR __MAX_WEEK = 
CALCULATE(
    SUM('table'[sales]),
    FILTER(
        ALL('table'), 'table'[week number] = MAX('table'[week number])
    )
)

RETURN 
DIVIDE( __MAX_WEEK - __PREV_WEEK, __PREV_WEEK)

But with this, I get the variation for the total in each row.

Here's what I get :

Stores	Sales week 1	Sales week 2	Variation week 1 - 2
Store 1	10€	20€	+13%
Store 2	10€	-10€	+13%
Store 3	10€	15€	+13%
Store 4	10€	20€	+13%
Total	40€	45€	+13%

Here's what I would like to have :

Stores	Sales week 1	Sales week 2	Variation week 1 - 2
Store 1	10€	20€	+100%
Store 2	10€	-10€	-200%
Store 3	10€	15€	+50%
Store 4	10€	20€	+100%
Total	40€	45€	+13%

Many thanks in advance for your help

J.

[Jo_Chrq · Accepted solution]

I just found the solution, which was actually quite simple.

I was using ALL so it ignored the stores (rows).

Should be ALLEXCEPT and exclude the the stores

Thanks anyway

J.

[amitchandak]

@Jo_Chrq , please refer my blog on week over week for this

https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

week of your choice

https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Powe...

[Jo_Chrq]

Hi @amitchandak 

Thank you for your reply. Unfortunately, I still have the same problem -> Each row gives me the total variation and not the variation for each store (each row)