cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pmdci
Advocate V
Advocate V

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?

 

Thanks in advance for your help.

 

Regards,

P.

1 ACCEPTED SOLUTION
greggyb
Resident Rockstar
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()

View solution in original post

8 REPLIES 8
akshay1193
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?

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. Smiley Sad Smiley Frustrated

Greg_Deckler
Super User
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])


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

You saved me! 🙂
Thank you !

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

greggyb
Resident Rockstar
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.

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.