Reply
Highlighted
Frequent Visitor
Posts: 9
Registered: ‎10-05-2016
Accepted Solution

Making fractions across survey data with DAX.

[ Edited ]

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.


Accepted Solutions
Member
Posts: 128
Registered: ‎09-29-2016

Re: Making fractions across survey data with DAX.

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]

 

View solution in original post


All Replies
Member
Posts: 128
Registered: ‎09-29-2016

Re: Making fractions across survey data with DAX.

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

Super Contributor
Posts: 4,721
Registered: ‎07-08-2016

Re: Making fractions across survey data with DAX.

@DatamindsMBM

 

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

Frequent Visitor
Posts: 9
Registered: ‎10-05-2016

Re: Making fractions across survey data with DAX.

Hey Herbert,

 

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

Frequent Visitor
Posts: 9
Registered: ‎10-05-2016

Re: Making fractions across survey data with DAX.

Hey Laurent,

 

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

Member
Posts: 128
Registered: ‎09-29-2016

Re: Making fractions across survey data with DAX.

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] )

 

Frequent Visitor
Posts: 9
Registered: ‎10-05-2016

Re: Making fractions across survey data with DAX.

@LaurentCouartou

 

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).

Nice try.png

Member
Posts: 128
Registered: ‎09-29-2016

Re: Making fractions across survey data with DAX.

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

Frequent Visitor
Posts: 9
Registered: ‎10-05-2016

Re: Making fractions across survey data with DAX.

Table relations

Table relations.png

Member
Posts: 128
Registered: ‎09-29-2016

Re: Making fractions across survey data with DAX.

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]