Distinct value of one column divided by grand total

larras — Thu, 13 Oct 2022 13:23:20 GMT

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.

Re: Distinct value of one column divided by grand total

Anonymous — Thu, 13 Oct 2022 14:25:38 GMT

HI,

divide(CALCULATE(COUNT(CRMModel[ID], CRMModel[SurveyName]="2019Q4"), countrows(CRMModel[ID]))

Re: Distinct value of one column divided by grand total

larras — Thu, 13 Oct 2022 14:33:00 GMT

Thank you for your quick reply. This is what I get when I use the above formula

Re: Distinct value of one column divided by grand total

Anonymous — Thu, 13 Oct 2022 14:41:04 GMT

Miss ) see the pic

Re: Distinct value of one column divided by grand total

larras — Thu, 13 Oct 2022 15:22:14 GMT

It looks like the DAX formula does not recognize the "2019Q4".

Re: Distinct value of one column divided by grand total

Anonymous — Thu, 13 Oct 2022 15:25:00 GMT

Give a pic of your table column it will be easier

topic Distinct value of one column divided by grand total in DAX Commands and Tips

Distinct value of one column divided by grand total

Re: Distinct value of one column divided by grand total

Re: Distinct value of one column divided by grand total

Re: Distinct value of one column divided by grand total

Re: Distinct value of one column divided by grand total

Re: Distinct value of one column divided by grand total