topic Re: Create a measure to calculate Weekly Index and should be dynamic with slicer selection in DAX Commands and Tips

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

santpras0 — Thu, 21 Nov 2024 09:07:29 GMT

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.

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

bhanu_gautam — Thu, 21 Nov 2024 09:57:24 GMT

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.

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

Poojara_D12 — Thu, 21 Nov 2024 12:09:14 GMT

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