Create a measure to calculate Weekly Index and should be dynamic with slicer selection

[santpras0]

I have simillar data like below

Sub-Cat	Manu	Brand	Product	Size	Year	Week	Sales
A	A1	AA1	AAA1	XL	1	1	10
A	A1	AA1	AAA1	XL	1	2	12
A	A1	AA1	AAA1	XL	2	1	12
A	A1	AA1	AAA1	XL	2	2	13
B	B1	BB1	BBB1	XXL	1	1	19
B	B1	BB1	BBB1	XXL	1	2	21
B	B1	BB1	BBB1	XXL	2	1	15
B	B1	BB1	BBB1	XXL	2	2	22
A	A1	AA1	AAA1	XXL	1	1	15
A	A1	AA1	AAA1	XXL	1	2	12
A	A1	AA1	AAA1	XXL	2	1	14
A	A1	AA1	AAA1	XXL	2	2	13

I will have all columns in slicers except for Year and Weeks.

I need to create a measure to display below o/p.

Let say If I select A in Sub-Cat, The o/p Should be

Week	Index
1	101
2	99

 Steps to acheive this.

Step 1:Sum the sales of all products which are under A by Year and Week.

Year	Week	Sum Of Sales
1	1	25
1	2	24
2	1	26
2	2	26

Step2: Calculate the average of sales which will be 25.25

Step3: Calculate the weekly Index which is sales/Avg

Year	Week	Sum Of Sales	Index
1	1	25	99
1	2	24	95
2	1	26	103
2	2	26	103

Step 4: Group by Week and average the Weekly Index of the sale which will be the final o/p given above.

I need to create this using a measure. I can't wrap my head around it. Any help would be hugely appreciated.

[Poojara_D12 · Accepted solution]

Hi @santpras0 

Sum of Sales by Week

Weekly Sales = 
SUM('Table'[Sales])

Average Sales Across All Weeks

Average Sales = 
AVERAGEX(
    SUMMARIZE(
        'Table',
        'Table'[Year],
        'Table'[Week],
        "WeeklySales", [Weekly Sales]
    ),
    [WeeklySales]
)

Weekly Index

Weekly Index = 
VAR CurrentWeeklySales = [Weekly Sales]
VAR AvgSales = [Average Sales]
RETURN
DIVIDE(CurrentWeeklySales, AvgSales, 0) * 100

Final Weekly Index (Grouped by Week)

Final Weekly Index = 
AVERAGEX(
    SUMMARIZE(
        'Table',
        'Table'[Week],
        "WeeklyIndex", [Weekly Index]
    ),
    [WeeklyIndex]
)

When you apply slicers (e.g., selecting SUB - CAT = A), the measures dynamically calculate:

Week Final Weekly Index

1	101
2	99

This is based on the grouping and averaging logic explained. Ensure the measures are applied in the correct visuals (e.g., a table or matrix) to display the desired results.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
YouTube: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS

[bhanu_gautam]

@santpras0 , Try using

Here is how you can create the necessary measures in Power BI:

Sum of Sales by Year and Week:
SumOfSales =
CALCULATE(
SUM('Table'[Sales]),
ALLEXCEPT('Table', 'Table'[Year], 'Table'[Week])
)

Average Sales:
AverageSales =
CALCULATE(
AVERAGE('Table'[Sales]),
ALLEXCEPT('Table', 'Table'[Year], 'Table'[Week])
)

Weekly Index:
WeeklyIndex =
DIVIDE(
[SumOfSales],
[AverageSales]
)
Final Measure to Group by Week and Average the Weekly Index:
DAX
FinalWeeklyIndex =
AVERAGEX(
SUMMARIZE(
'Table',
'Table'[Week],
"WeeklyIndex", [WeeklyIndex]
),
[WeeklyIndex]
)

To display the final output, you can create a table visual in Power BI and add the Week column and the FinalWeeklyIndex measure.