I have this two measures, the first one calculates the average sales of the full year and show it in my graph as a fixed value; the second one calculates and show the same but for the prior year.
Fixed= DIVIDE(
CALCULATE(DIVIDE([Sales], 50), ALL('DataMain')),
CALCULATE(DISTINCTCOUNT('Calendar'[Month]), ALLEXCEPT('Calendar', 'Calendar'[Year]))
)
FixedPriorYear =
DIVIDE (
CALCULATE (
DIVIDE([Sales], 20),
ALL ( 'DataMain' ),
PARALLELPERIOD('Calendar'[Date], -1, YEAR)
),
CALCULATE (
DISTINCTCOUNT ( 'Calendar'[Month]),
ALL ('Calendar'),
PARALLELPERIOD('Calendar'[Date], -1, YEAR)
)
)
The problem that i want to solve is that, the averge is correctly calculated and displayed, but when i change the the year using a slicer, the average is not recalculating.
Data example year 2023: | Month | Sales | | -------- | -------------- | | January | 100 | | February | 200 | | March | 300 | | April | 400 | | May | 500 | | June | 600 | | July | 700 | | August | 800 | | September| 900 | | October | 1000 | | November | 1100 | | December | 1200 |
the sum of these values is 7800, the average is 650. With those measures i have the same result (650) to display it in each month. But when i change the year in my slicer, the result dont change.
Here´s a image to show what i have at the moment. I only need that when i change the year in my slicer, the average recalculate itself.
This is the sample data I used for test.
year | month | Sales |
---|---|---|
2023 | Jan | 10 |
2023 | Feb | 20 |
2023 | Mar | 30 |
2023 | Apr | 40 |
2023 | May | 50 |
2023 | Jun | 60 |
2023 | Jul | 70 |
2023 | Aug | 80 |
2023 | Sep | 90 |
2023 | Oct | 100 |
2023 | Nov | 110 |
2023 | Dec | 120 |
2022 | Jan | 130 |
2022 | Feb | 140 |
2022 | Mar | 150 |
2022 | Apr | 160 |
2022 | May | 170 |
2022 | Jun | 180 |
2022 | Jul | 190 |
2022 | Aug | 200 |
2022 | Sep | 210 |
2022 | Oct | 220 |
2022 | Nov | 230 |
2022 | Dec | 240 |
you can create a measure
MEASURE =
DIVIDE (
CALCULATE (
SUM ( 'Table'[Sales] ),
FILTER ( ALL ( 'Table' ), 'Table'[year] = MAX ( 'Table'[year] ) )
),
CALCULATE (
COUNT ( 'Table'[month] ),
FILTER ( ALL ( 'Table' ), 'Table'[year] = MAX ( 'Table'[year] ) )
)
)