Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DatamindsMBM
Frequent Visitor

Making fractions across survey data with DAX.

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.

1 ACCEPTED SOLUTION

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

10 REPLIES 10
v-haibl-msft
Employee
Employee

@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

Hey Herbert,

 

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

LaurentCouartou
Solution Supplier
Solution Supplier

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

Hey Laurent,

 

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

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

 

@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

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

Table relations

Table relations.png

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]

 

This worked! I am now filtering correctly, so I get the correct fractions across the data. Except I still use [TimeCompleted] instead of respondentID because respondentID is only one id for the same person that fills in the survey. The only column that tracks surveys completed is [TimeCompleted]. I don't want to show the result, because it is ruined by some null values in my data. But these are easily removed, and this solution solved my problem. thank you so much! Kudo for you 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.