I need help figuring out a DAX formula. My data consists of two different surveys from 2019 and 2022. The questions are the same, but the number of responses differ between the years. When I want to compare a survey results of one question between the two years both compare to the Grand Total of all responses collectively because they are both listed in the same collumn.
My Table is called [CRMModel] and the field I want to calculate is called [SurveyName].
[SurveyName] consists of a total of 407 rows of text with two distinct values, one is "2019Q4" and the other is "Employee Survey 22". [ID] is the total number of rows (individual responses) - but in that group both "2019Q4" and "Employee Survey 22" are included.
When I compare the results from one (2019) against the other (2022), it compares both against the grand total.
[SurveyName] (includes below)
- Distinctvalue1 "2019Q4" = Quantity 292 pcs (static)
- Distinctvalue2 "Employee Survey 22" = Number 115 (and increasing)
[ID] = 407 pcs
The Ggrand total for Count of 'CRMModel'[ID] includes both "2019Q4" and "Employee Survey 22" which is = 407
I would like "2019Q4" to count against 292 and "Employee Survey 22" against 115 (or whatever that total ends up being). Is there any DAX formula that can help me with that?
I've tried the following:
Count of 2019Q4 = CALCULATE(COUNT(CRMModel[ID], FILTER(CRMModel, CRMModel[SurveyName]="2019Q4"))%2019Q4 =[Count of 2019Q4]/[ID]
But get the following error "The following syntax error occured during parsing: Invalid token, Line 1, Offset 79,%
I appriciate some help to figure out the correct formula. Thank you.
