cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
enterprised1 Frequent Visitor
Frequent Visitor

Display Percent rather than Count

Hi,

 

This seems like a simple thing to do in theory, but I can't seem to make it happen in Power BI.  If anybody has any suggestions, it would be greatly appreciated!  Here's the scenario:

 

  • I have a Report that pulls data from an Excel file on Onedrive. 
  • I have an ID field that I want to count - I use the Card visualization and change the ID field to 'Count' and I get the total.  Great!
  • I now want to add a filter where Amount = 800.  I drag the Amount field to the Visual level filters, set it to 800 and I get a new total.  Also Great! 
  • However, I need to report it as a percent of the Total.

i.e. Count of all IDs in the Excel file is 1000.  Count of all IDs where Amount=800 is 778.  How can i display it as 77.8% rather than just 778?

 

Soooo Confused.  Help! Smiley Happy

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-micsh-msft
Microsoft

Re: Display Percent rather than Count

Hi enterprised1,

 

We should write measure to show the correct percentage. Please follow the steps below to see if it would work:

  1. Under the data View, create a measure to calculate the count of Amount=800;
  2. Count800 = calculate(count(‘Table’[ID]), ‘Table’[Amount]=800)
  3. Create another measure to calculate the percentage:
  4. Precent = Divide([Count800],COUNT(‘Table’[ID]))
  5. Under the report View, in the Fields, click Precent, then click on % to format it. Select Card Visual and put the Precent into the values, it should show the correct percentage:

12.PNG

Using the quick calc here would always show 100%, as the total count is also filtered, but not the original total.

If any further help needed, please feel free to post back.

Regards

View solution in original post

7 REPLIES 7
ankitpatira Super Contributor
Super Contributor

Re: Display Percent rather than Count

@enterprised1 Click dropdown for the field used for Card visual as below and then choose 'Percent of grand total" under Show value as.

 

Capture.PNGCapture2.PNG

Microsoft v-micsh-msft
Microsoft

Re: Display Percent rather than Count

Hi enterprised1,

 

We should write measure to show the correct percentage. Please follow the steps below to see if it would work:

  1. Under the data View, create a measure to calculate the count of Amount=800;
  2. Count800 = calculate(count(‘Table’[ID]), ‘Table’[Amount]=800)
  3. Create another measure to calculate the percentage:
  4. Precent = Divide([Count800],COUNT(‘Table’[ID]))
  5. Under the report View, in the Fields, click Precent, then click on % to format it. Select Card Visual and put the Precent into the values, it should show the correct percentage:

12.PNG

Using the quick calc here would always show 100%, as the total count is also filtered, but not the original total.

If any further help needed, please feel free to post back.

Regards

View solution in original post

enterprised1 Frequent Visitor
Frequent Visitor

Re: Display Percent rather than Count

@ankitpatiraThanks for the reply.  Unfortunately, I don't see the "Quick Calc" option you have in your picture.  Those menu options seem to vary depending on the underlying data type.  I'm using the Number type for the ID field, and here is what I see:

 

number.png

 

If I select a Text field, here is what I see:

text.png

 

What field type were you using to get the "Quick Calc" field?

 

Thanks!

enterprised1 Frequent Visitor
Frequent Visitor

Re: Display Percent rather than Count

@v-micsh-msft  Thanks for the suggestion!  I'm not using Power BI Desktop, but I believe I can accomplish adding the Measure directly in the Excel file.  I'll give that a try and will post back if I run into any issues. 

 

Any ideas on if or how this can be accomplished within the Power BI service?  I would rather be able to do it from the Power BI interface than go messing with the Excel file if I can avoid it.

 

Thanks!

Microsoft v-micsh-msft
Microsoft

Re: Display Percent rather than Count

Hi

Apologize to say that currently it is not available to create calculated column or a measure in Power BI Service.

You may take a try with Power Bi desktop, download it from here:

https://powerbi.microsoft.com/en-us/desktop/

Regards

enterprised1 Frequent Visitor
Frequent Visitor

Re: Display Percent rather than Count

Hi Michael,

 

I thought that would be the case.  I didn't see any way of adding measures from the Power BI service. 

 

However, I was able to add the measures within the data model of the Excel file using the steps you provided, so all's good.  Thanks for your help!

Highlighted
kaleydvorak Frequent Visitor
Frequent Visitor

Re: Display Percent rather than Count

I have specified the column is a percent in the data table but it is showing as a decimal in the card. And under modeling the formatting options are grayed out, do you know why that would be?

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (3,816)