Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Matthias93
Helper III
Helper III

Calculating the submittance rate

Hi,

 

In my dataset I want to be able to see the percentage of people that respond to a certain survey. Do do this I have 2 relevant columns. One with an Id for every form  (so 1 unique id per form, with a different one for every person) and one with the submittance date of the form. This submittance date can also be blank. I would like to create a chart that gives me the response rate for the from/ So basically: Count of Id's / count of submittance dates (counting 0 when the submittance date is blank). I find this hard to do since the date is in a date/time format. Can anybody please help me?

 

Thanks in advance,

Matt

Date.JPGId.JPG

1 ACCEPTED SOLUTION

Hi @Matthias93,

 

I i don't think you can get the response rate for each month, since if the submitted is blank, Power bi cannot determine which month this record belog to. So we can only get the over all response rate.

Capture.PNG

If each id-check-in belog to a month, then you can get the response rate for each month.

ResponseRate = CALCULATE(COUNTA('Check-in'[Submitted]),ALLEXCEPT('Check-in','Check-in'[Month]))/CALCULATE(COUNTA('Check-in'[Id-check-in]),ALLEXCEPT('Check-in','Check-in'[Month]))

Capture1.PNGCapture2.PNG

Regards,

Charlie Liao

View solution in original post

8 REPLIES 8
Vvelarde
Community Champion
Community Champion

Hi @Matthias93

 

is this what you want?Sin título.png




Lima - Peru

This is very close to what I want, I'm quite a beginner with Power BI. Is this the right measure? (my table is called Check-in.)

 

Response rate = COUNT(Check-in [Submitted])/COUNT(Check-in[Id-check-in]). This gives me a 'token literal expected' error.

 

Thanks for helping!

 

Matt

@Matthias93

 

where are creating the measure

 

 

you can create in this place

new measurenew measure




Lima - Peru

@Vvelarde

 

I indeed filled it in in that area as follows: Capture.JPG

The error is saying that can't find a table name Check-in ...try to put 'Check-in'




Lima - Peru

Hi @Vvelarde I was able to create this measure, but when I want to use it in a visualization, for expample for the response rate per month I get the following error:

 

Error Message:

MdxScript(Model) (1, 49) Calculation error in measure 'Check-Ins'[Response rate]: The function COUNT takes an argument that evaluates to numbers or dates and cannot work with values of type String.

Stack Trace:

 

Invocation Stack Trace:

 

Activity ID
46ee4405-bcff-4067-8fc7-ad0044f23eed

Time
Tue Oct 04 2016 09:10:47 GMT+0200 (Romance Daylight Time)

Version
2.38.4491.642 (PBIDesktop)

Error Code
rsDataShapeProcessingError

OData Error Message
Failed to execute the query.

 

Hi @Matthias93,

 

I i don't think you can get the response rate for each month, since if the submitted is blank, Power bi cannot determine which month this record belog to. So we can only get the over all response rate.

Capture.PNG

If each id-check-in belog to a month, then you can get the response rate for each month.

ResponseRate = CALCULATE(COUNTA('Check-in'[Submitted]),ALLEXCEPT('Check-in','Check-in'[Month]))/CALCULATE(COUNTA('Check-in'[Id-check-in]),ALLEXCEPT('Check-in','Check-in'[Month]))

Capture1.PNGCapture2.PNG

Regards,

Charlie Liao

This worked perfectly, thank you so much

Helpful resources

Announcements
Europe Fabric Conference

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.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors