Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to 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
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
@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.
Proud to be a Super User! |
|
User | Count |
---|---|
25 | |
12 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
12 | |
11 | |
9 | |
6 |