Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. 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!!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
24 | |
21 | |
18 | |
14 | |
11 |
User | Count |
---|---|
44 | |
35 | |
25 | |
22 | |
22 |