Wrong total value DAX

[2FG]

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!

[Shishir22]

Hello @2FG ,

Can you try -

TEST =
IF (
    HASONEVALUE ( SURVEY[CITY] ),
    [1a Number of respondents (last value)],
    SUMX ( VALUES ( SURVEY[CITY] ), [1a Number of respondents (last value)] )
)

Please Accept it as the solution if it solves your issue. Kudos are also appreciated.

Cheers,

Shishir

[2FG]

Hi @Shishir22 , 
Unfortunately that does not work either. With this measure the value for Cityname3 becomes blank and the total is still 170.

[Anonymous]

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.

[2FG]

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.