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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
santpras0
Frequent Visitor

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

I have simillar data like below

Sub-CatManuBrandProductSizeYearWeekSales
AA1AA1AAA1XL1110
AA1AA1AAA1XL1212
AA1AA1AAA1XL2112
AA1AA1AAA1XL2213
BB1BB1BBB1XXL1119
BB1BB1BBB1XXL1221
BB1BB1BBB1XXL2115
BB1BB1BBB1XXL2222
AA1AA1AAA1XXL1115
AA1AA1AAA1XXL1212
AA1AA1AAA1XXL2114
AA1AA1AAA1XXL2213

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

WeekIndex
1101
299

 Steps to acheive this.

 

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

YearWeekSum Of Sales
1125
1224
2126
2226

Step2: Calculate the average of sales which will be 25.25

Step3: Calculate the weekly Index which is sales/Avg

YearWeekSum Of SalesIndex
112599
122495
2126103
2226103

 

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.

 

 

 

1 ACCEPTED SOLUTION
Poojara_D12
Super User
Super User

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

1101
299

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

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 - Proud to be a Super User
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS

View solution in original post

2 REPLIES 2
Poojara_D12
Super User
Super User

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

1101
299

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

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 - Proud to be a Super User
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS
bhanu_gautam
Super User
Super User

@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.




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.