How to get Success rate of out Grand Total

[AmirFirdaus9509]

Hi , I need help in solving an issue .

I am fairly knowledgable in Excel but still new in Power Bi.
I have export a data from excel file that contain three header. Month (January , February , March), Country (England , Brazil , US ) and Status (Response/No Response). 

Here is a reference to what i am trying to project in Power Bi , where i would want the graph to show the Response Percentage out of the grand total

From here , i have use a bar graph and try to get "Response" percentage out of the grand total of . But the Power Bi is taking the amount of Response/No Response. How can i exclude the "No Response" and get the percentage as shown in the Pivot Table.

Thank you very much

[v-easonf-msft · Accepted solution]

Hi， @AmirFirdaus9509 

I would suggest you to create measure as below:

Measure_count of month = CALCULATE(COUNT('Table'[Month]),ALLEXCEPT('Table','Table'[Month],'Table'[Country]))

Measure_count of Response Status = CALCULATE(COUNT('Table'[Month]),FILTER(ALLEXCEPT('Table','Table'[Month],'Table'[Country]),'Table'[Response Status]="Responded"))

Measure_percentage = [Measure_count of Response Status]/[Measure_count of month] 

If you just  want to show "Responded" Percentage out of grand total in your original table, you can also try calculated columns as below:

Count_of_month = CALCULATE(COUNT('Table'[Month]),ALLEXCEPT('Table','Table'[Month]))

Count_of_Response Status(month) = CALCULATE(COUNT('Table'[Month]),FILTER(ALLEXCEPT('Table','Table'[Month]),'Table'[Response Status]="Responded"))

Percentage = 'Table'[Count_of_Response Status(month)]/'Table'[Count_of_month]

Please check my  attached pbix file for more details.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Angith_Nair]

Hi @AmirFirdaus9509 ,

Try to use this code..

Measure =
CALCULATE (
            DIVIDE( [Count of Response Status], [Count of Month] ),
            Table[Status] = "Response"
)

and format the measure as % as shown below...

[AmirFirdaus9509]

Hi @Angith_Nair  , 
I think the code is based on the Pivot Table from Excel Source , 
I have try and alter the data based on this table in the Power Bi Query

Is it possible to show "Responded" Percentage out of grand total from here?

Thank you for the help