cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## Distinct value of one column divided by grand total

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.

5 REPLIES 5
Anonymous
Not applicable

Miss ) see the pic

New Member

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

Anonymous
Not applicable

Give a pic of your table column it will be easier

Anonymous
Not applicable

HI,

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

New Member

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.