Display Percent rather than Count

[enterprised1]

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!

Thanks!

[v-micsh-msft · Accepted solution]

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:

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

[Anonymous]

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?

[enterprised1]

@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!

[v-micsh-msft]

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]

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!

[ankitpatira]

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

[enterprised1]

@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:

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

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

Thanks!