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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
rgu101
Helper I
Helper I

Percent of Column Total incorrect

I'm calculating the response rate for this survey question by using Count (Distinct) for each survey ID. The counts (n-sizes) are correct but when I tried to show values as Percent of Column Total, the individual response rates are incorrect and thus don't add to 100%. I do have filters on, but when I double checked with the raw data, I can confirm the n-sizes are accurate.

rgu101_0-1708973413360.png

Should I create a calculated measure to get an accurate number?

Thanks in advance.

1 ACCEPTED SOLUTION
selimovd
Super User
Super User

Hey @rgu101 ,

 

why don't you just calculate it with DAX?

Percentage =
VAR _DistinctCount = [Your count measure]
VAR _Total = CALCULATE ( [Your count measure], ALL ( myTable[Column with agree disagree] ) )
RETURN
    DIVIDE ( _DistinctCount, _Total )

 

Then format as percent and you should be good to go.

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic
 

View solution in original post

2 REPLIES 2
selimovd
Super User
Super User

Hey @rgu101 ,

 

why don't you just calculate it with DAX?

Percentage =
VAR _DistinctCount = [Your count measure]
VAR _Total = CALCULATE ( [Your count measure], ALL ( myTable[Column with agree disagree] ) )
RETURN
    DIVIDE ( _DistinctCount, _Total )

 

Then format as percent and you should be good to go.

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic
 

Response Rate = 
VAR _DCount =
DISTINCTCOUNT('Raw Data'[Visit ID])

VAR _QTotal =
CALCULATE(
    DISTINCTCOUNT('Raw Data'[Visit ID]),
    ALLEXCEPT('Raw Data',
        'Raw Data'[QuestionText],
        'Raw Data'[Date],
        DateTable)
)

RETURN
DIVIDE(_DCount,_QTotal)

Thank you for the help. Settled with a calculated measure because i couldn't find any solutions for why the column total was incorrect.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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