CODEWITHSUNDEEP
daxpowerpivot
Ingenious1
Ingenious1

Reputation: 1

Difference % between 2 columns in Pivot table using Dax measure within Power Pivot

I can't find a formula which calculates the difference % between 2 columns in my pivot table using Power Pivot. See Data in data model:

enter image description here

See Pivot table where I want to create a measure :

enter image description here

I want to calculate the sum of sales for week 1 in 2024 against the sum of sales for week 1 in 2023 and get the difference %. So in the pivot table above I want to add a column after each week which shows the difference % between the 2 weeks for each week up to week 52.

In a normal Pivot table I would create a calculated field and choose Difference % from. See below, calculated field in normal pivot table :

enter image description here

How can I recreate this using a Dax formula?

Upvotes: 0

Views: 362

Answers (1)

Sam Nseir
Sam Nseir

Reputation: 12111

If you were to create a measure and add it to the pivot table, you will then have it present in each & every column, which I don't think you want.

If you can live without having the Date column then I would suggest you only have the Week Number column in your Pivot and drop the Year and Week Date columns. And instead create three new Measures for "2024", "2023", "YoY %".

Sales 2024 := CALCULATE(SUM('YourTable'[TOTAL]), 'YourTable'[YEAR] = 2024)

Sales 2023 := CALCULATE(SUM('YourTable'[TOTAL]), 'YourTable'[YEAR] = 2023)

Sales YoY %:= DIVIDE([Sales 2024], [Sales 2023]) - 1

Then add these three new measures to your Values well in your Pivot table.

Upvotes: 1

Related Questions