DAX Code to calculate Weekly Sales Index based on Different slicers

[santpras0]

I have data which is simllar to attached File.

I will have slicers for each column except Sales Week and Year. If I slice and dice, I need change in visual. I have give example below.

If I select A in the sub-cat. I need below o/p. 

Step 1: I need to sum the sales of all products by year and week.

The o/p  of this step would be

Year	Week	Sum Of Sales
1	1	25
1	2	24
1	3	19
1	4	23
2	1	26
2	2	26
2	3	22
2	4	18

Step2: Calculate Average of sales which will be 22.875

Step 3: Divide the Weekly Sales with AVG and create Index. Below will be o/p

Year	Week	Sum Of Sales	Index
1	1	25	109
1	2	24	105
1	3	19	83
1	4	23	101
2	1	26	114
2	2	26	114
2	3	22	96
2	4	18	79

Step 4 is the final o/p I need which is to group by week and average the index.

Week	Avg Index
1	111
2	109
3	90
4	90

Can I somehow do all this in a measure?

The o/p would be again different If I select just XXL in Size.

I just cannot wrap my head around it. Any help will be hugely appreciated.

Thanks,

[lbendlin · Accepted solution]

Index = divide(sum('Table'[Sum Of Sales]),CALCULATE(AVERAGE('Table'[Sum Of Sales]),REMOVEFILTERS('Table'[Week])))

Avg Index = 
var a = ADDCOLUMNS(SUMMARIZE('Table',[Year],[Week]),"i",CALCULATE([Index]))
var b = SUMMARIZE(a,[Week],"ai",AVERAGEX(a,[i]))
return AVERAGEX(b,[ai])

[VahidDM]

Hi @santpras0 

Yes, you can achieve this using DAX measures that dynamically respond to slicers. Here's how:

1. Calculate Total Sales by Year and Week:

TotalSales = SUM('YourTable'[Sales])

2. Calculate Average Sales (Considering Slicers):

DAX
Copy code
AverageSales =
CALCULATE(
AVERAGE('YourTable'[Sales]),
ALLSELECTED('YourTable')
)

3. Compute Weekly Sales Index:

WeeklySalesIndex =
DIVIDE([TotalSales], [AverageSales]) * 100

4. Calculate Average Index by Week:

AvgIndexByWeek =
CALCULATE(
AVERAGEX(
VALUES('YourTable'[Year]),
[WeeklySalesIndex]
),
ALLSELECTED('YourTable'[Year])
)

Usage:

Place Week on the axis of your visual.
Use [AvgIndexByWeek] as the value.

Explanation:

• [TotalSales] sums sales for each Year and Week, respecting slicers.
• [AverageSales] calculates the average sales over all selected data.
• [WeeklySalesIndex] creates the index by comparing weekly sales to the average.
• [AvgIndexByWeek] averages the index across years for each week.

This approach ensures your visual updates dynamically based on slicer selections, providing the desired output.

Note: Replace 'YourTable' and column names with your actual table and column names.

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

Appreciate your Kudos!! 

LinkedIn|Twitter|Blog |YouTube