Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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
Year | Week | Sum Of Sales |
1 | 1 | 25 |
1 | 2 | 24 |
1 | 3 | 19 |
1 | 4 | 23 |
2 | 1 | 26 |
2 | 2 | 26 |
2 | 3 | 22 |
2 | 4 | 18 |
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
Year | Week | Sum Of Sales | Index |
1 | 1 | 25 | 109 |
1 | 2 | 24 | 105 |
1 | 3 | 19 | 83 |
1 | 4 | 23 | 101 |
2 | 1 | 26 | 114 |
2 | 2 | 26 | 114 |
2 | 3 | 22 | 96 |
2 | 4 | 18 | 79 |
Step 4 is the final o/p I need which is to group by week and average the index.
Week | Avg Index |
1 | 111 |
2 | 109 |
3 | 90 |
4 | 90 |
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,
Solved! Go to Solution.
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])
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])
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:
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!!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
14 | |
11 | |
7 |
User | Count |
---|---|
25 | |
24 | |
12 | |
12 | |
11 |