Forum Discussion
Wrong total value DAX
Hi! Hope someone can help me with this question.
BACKGROUND:
I have two tables:
Calendar =
ADDCOLUMNS (
CALENDAR (DATE(2016,1,1), DATE(YEAR(TODAY())-1,12,31)),
"Year", YEAR ( [Date] ))
...and Survey:
Year | City | Respondent ID | Question 1a | Question 1b |
2020 | Cityname1 | 123456 | 3 | 2 |
2020 | Cityname1 | 123457 |
| 1 |
2020 | Cityname1 | 123458 | 4 |
|
2020 | Cityname1 | 123459 | 5 | 5 |
I have also created a column in Survey that has a many-to-one relationsship to Calendar:
Dates = DATE(Survey[Year],1,1)
MEASURES:
1a Number of respondents =
CALCULATE(
DISTINCTCOUNT(Survey[Respondent ID]),
Survey[Question 1a] <> BLANK()
)
1a Number of respondents (last value) =
VAR NumberLY= CALCULATE([1a Number of respondents], SAMEPERIODLASTYEAR(Calendar[Date]))
RETURN
IF(ISBLANK([1a Number of respondents]), NumberLY, [1a Number of respondents])
QUESTION:
If you choose a year in the report and a city has a value for [1a Number of respondents], you should see that value. But if it hasn't a value the chosen year, you should get the value from the year before. This works fine when I have a filter on cities. But when it comes to the total level, it only shows the values from the chosen year. How can I adjust my measure [1a Number of respondents (last value)] (or create a new measure) to show a mix of values from the chosen year and the year before?
EXAMPLE:
I select year 2020 in the report (from Calendar) and get the following result:
City | 1a Number of respondents (last value) |
Cityname2 | 170 |
Cityname3 | 166 |
Total | 170 |
The value for Cityname2 is from 2020 and the value for Cityname3 is from 2019. Total value should be 170 + 166 = 336 and not 170.
Thanks in advance!
4 Replies
Hi 2FG ,
You can get the correct total value by creating a new measure.
New measure = SUMX('TableName', [1a Number of respondents (last value)] )
Then you put the new measure into it, I'm sure you'll get this.
City
1a Number of respondents (last value)
New Measure
Cityname2
170
170
Cityname3
166
166
Total
170
336
Please give it a try.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi!
No, sorry, it doesn't work as long as I have a filter on the year. Then I still get 170 in the total level.
