Forum Discussion

pmdci's avatar
pmdci
Icon for Advocate V rankAdvocate V
10 years ago
Solved

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.

Need Help
  • greggyb's avatar
    greggyb
    10 years ago

    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

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    10 years ago
    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])

    • oristides's avatar
      oristides
      Icon for Helper I rankHelper I
      10 years ago

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

  • greggyb's avatar
    greggyb
    Icon for Resident Rockstar rankResident Rockstar
    10 years ago

    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()

    • pmdci's avatar
      pmdci
      Icon for Advocate V rankAdvocate V
      10 years ago

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

       

      Regards,

      P.

  • akshay1193's avatar
    akshay1193
    New Member
    10 years ago

     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?

    • pmdci's avatar
      pmdci
      Icon for Advocate V rankAdvocate V
      9 years ago

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

    • jubrna01's avatar
      jubrna01
      Frequent Visitor
      9 years ago

      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