Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
santpras0
Frequent Visitor

DAX Code to calculate Weekly Sales Index based on Different slicers

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

YearWeekSum Of Sales
1125
1224
1319
1423
2126
2226
2322
2418

 

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

YearWeekSum Of SalesIndex
1125109
1224105
131983
1423101
2126114
2226114
232296
241879

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

 

WeekAvg Index
1111
2109
390
490

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,

 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

lbendlin_0-1732223227970.png

 

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])

 

View solution in original post

2 REPLIES 2
lbendlin
Super User
Super User

lbendlin_0-1732223227970.png

 

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
Super User
Super User

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 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.