- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

# Making fractions across survey data with DAX.

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-05-2016 06:11 AM - edited 10-07-2016 04:34 AM

Hello Power BI Community,

I am making a report displaying survey data. There are scalar questions, multi questions, and categorical questions. I'd like to make fractions across these questions in one table using DAX measures. I'd like to know the percentage of replies on both categorical and multi questions. I have no issues reporting on scalar values, and it's pretty simple with categorical questions as they are binary, and can only sum to 100%. but multi questions can sum to more than 100%.

An example: I want to examine my distributors. Perhaps the knowledge of my product. So my multi question is "Distributor knowledge about my product". The possible responses are:

- Knows how to install my product

- Knows how to use additionally accessory for my product

- Knows the difference between my product and my competitors product

- Has seen my particular demonstration of my product

- Knows how my product is produced.

- Owns the product privately.

One distributor might mark 4 of them, another might mark all of them, and a third might only mark 2. Nevertheless there will only be 3 respondents, but 4+6+2=11 reponses, 12/3=4. However there can only be 100% or less within each question, so I would like to know the percentage within each response. for example 2 respondents responded with "Knows how my product is produced". That makes it 2/3=0,66, since there were 3 respondents. Next step is to visualize all of this in a table. Now I did find a way to do this with DAX measures.

"Count of answer" = COUNTROWS(ReportAnswer) and

"Count of completed surveys" = COUNTROWS(ALLSELECTED(ReportSurveyRespondent[TimeCompleted])).

Then writing "% of Total" = Count of answer / Count of compelted sureys.

There is a problem here though. In the report I would like to filter on different distributors, but I am not able to do that, because I used the ALLSELECTED-function. And I cannot find any other way to do the "% of Total", so that I might filter anyway. If I use Count of completed = DISTINCTCOUNT(ReportSurveyRespondent[TimeCompleted]), I get responses/respondents=1 every time, because I filter between the questions, when I insert them into a table.

Pew, that was a lot. I hope you guys can help me out. Picture below of what I have now, and how I would like it to look like.

Solved! Go to Solution.

Accepted Solutions

## Re: Making fractions across survey data with DAX.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-07-2016 03:13 AM

Does this work better?

Nr of respondents = DISTINCTCOUNT(ReportAnswer[RespondentID]) Nr of respondents - Total = CALCULATE( [Nr of respondents] , ALLEXCEPT( ReportAnswer , ReportSurveyRepondent ) ) % of Total = DIVIDE( [Nr of respondents], [Nr of respondents - Total]

All Replies

## Re: Making fractions across survey data with DAX.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-05-2016 08:26 AM

Do you get the expected results, if you use *ALLSELECTED(ReportSurveyRespondent)* instead of *ALLSELECTED(ReportSurveyRespondent[TimeCompleted]))*?

## Re: Making fractions across survey data with DAX.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-05-2016 09:36 PM

Maybe you can first get the selected distributor name, then use DISTINCTCOUNT with filter in it like below.

Count of completed surveys = VAR Distributor = IF ( HASONEVALUE ( 'ReportShop'[Shop] ), CALCULATE ( VALUES ( 'ReportShop'[Shop] ) ) ) RETURN ( CALCULATE ( DISTINCTCOUNT ( 'ReportSurveyRespondent'[TimeCompleted] ), FILTER ( ALL ( ReportShop ), 'ReportShop'[Shop] = Distributor ) ) )

Best Regards,

Herbert

## Re: Making fractions across survey data with DAX.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-07-2016 01:02 AM

Hey Herbert,

Unfortunately the syntax is flawed in the return function. Not sure where.

## Re: Making fractions across survey data with DAX.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-07-2016 01:03 AM

Hey Laurent,

unfortunately this gives me responses/respondents=1 in my table.

## Re: Making fractions across survey data with DAX.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-07-2016 01:28 AM

Thinking about it, you want to divide the number of respondants to a specific answer / question by the total number of respondants. That is, you want to ignore filters applied to answers / questions.

Assuming there is a *ReportAnswer[TimeCompleted] *and you use *TimeCompleted* to identify respondants:

Nr of respondants = DISTINCTCOUNT(ReportAnswer[TimeCompleted]) Nr of respondants - Total = CALCULATE( [Nr of respondants] , ALL( ReportAnswer ) , ALL( ReportQuestion ) ) % of Total = DIVIDE( [Nr of respondants], [Nr of respondants - Total] )

## Re: Making fractions across survey data with DAX.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-07-2016 01:42 AM

That was actually a clever way to do it. When not filtering it works exactly like my initial solution.

Unfortunately, as with my initial solution, you can't filter on shops (or distributors, as I call them in my example).

## Re: Making fractions across survey data with DAX.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-07-2016 02:36 AM

Can you post a picture of how your tables are related? (I should have started with asking this.)

## Re: Making fractions across survey data with DAX.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-07-2016 02:41 AM

Table relations

## Re: Making fractions across survey data with DAX.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-07-2016 03:13 AM

Does this work better?

Nr of respondents = DISTINCTCOUNT(ReportAnswer[RespondentID]) Nr of respondents - Total = CALCULATE( [Nr of respondents] , ALLEXCEPT( ReportAnswer , ReportSurveyRepondent ) ) % of Total = DIVIDE( [Nr of respondents], [Nr of respondents - Total]