cancel
Showing results for
Did you mean:

Showing percentage values in visuals

Hi there,

I have created measures in the past based on COUNTX which when combined with COUNTROWS I can create some percentage measures. For instance if I do a count of all rows where PRODUCT = "Apple" and divide this by COUNTROWS, then I can get a percentage. So far so good.

But one thing that has been driving me nuts is that I now want to simply show the percentages on a bar chart regardless of any categorical variable. Consider this very simple table (only 2 rows and 2 columns) as an example:

NAME, TOTAL SALES

John, 390.00

Andy, 210,00

So what I would like to do have a bar chart that instead of having the values 350.00 for the bar "John" and 250.00 for the bar "Andy",  it shows 65% for John and 35% for Andy.

Is that possible?

Regards,

P.

1 ACCEPTED SOLUTION
Resident Rockstar

You can't have totally dynamic percentages. You must define a base measure to use as numerator, and a measure that removes some filter context to create a denominator, you can then calculate the percentage using these components.

The following idiom should get you there:

```Base =
COUNTROWS( 'Table' )

Denominator =
CALCULATE(
[Base]
,ALLSELECTED()
)

Percentage =
DIVIDE( [Base], [Denominator] )```

Depending on your specific use case you may need to call out specific columns or tables in the ALLSELECTED()

8 REPLIES 8
New Member

How do I show both the percentage as well as a number on Power BI, for example if 200 fruits are there and 55% of them were apples; How do I show 110 apples (55%) or something of this sort in a single visual using Power BI?

Frequent Visitor

HI,

i do not know if this is what you are asking for. but to to the Value field-->fast calculation--> in the fast calculation box choose value shown as percentage

@akshay1193, short answer is that you can't. In this sense Power BI is light-years behind Excel.

Super User
You should be able to create a calculated column:

PERCENT = [TOTAL SALES] / SUM([TOTAL SALES])

format as %.

As a measure:

PERCENT = SUM([TOTAL SALES]) / SUMX(ALL(table),[TOTAL SALES])

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
New Member

You saved me! 🙂
Thank you !

Helper I

Hello how do you create a percentage measure ase on Highlighted vs Non Highlited in Tree or a bar plot?

Resident Rockstar

You can't have totally dynamic percentages. You must define a base measure to use as numerator, and a measure that removes some filter context to create a denominator, you can then calculate the percentage using these components.

The following idiom should get you there:

```Base =
COUNTROWS( 'Table' )

Denominator =
CALCULATE(
[Base]
,ALLSELECTED()
)

Percentage =
DIVIDE( [Base], [Denominator] )```

Depending on your specific use case you may need to call out specific columns or tables in the ALLSELECTED()

This worked beautifuly! Exactly what I was looking for. Thanks a lot for your help.

Regards,

P.