Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mahadeva_raobc
Frequent Visitor

Percent of column Total

Hi All,

 

Iam new to Power BI, is there a way to display % instead of number in stacked bar chart. I want to display column total % and not Percent of Grand Total

1 ACCEPTED SOLUTION

@mahadeva_raobc,

Create the following measure in your table.

Perent of Total = 
DIVIDE (
    COUNTA(Sheet1[CIP2]),
    CALCULATE ( COUNTA(Sheet1[CIP2]), ALLEXCEPT (Sheet1,Sheet1[Week]) )
)



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

11 REPLIES 11
Greg_Deckler
Super User
Super User

Not entirely sure what you are after. If you click the down arrow in the Values area for your column, you should get "Show value as" and there are options like percent of grand total, percent of column total, percent of row total (in a matrix for example) and then there are Quick Measures, which also have some percent options. I take it you have multiple stacked columns with a single measure? Additional information would be helpful.


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

I have successfully implemented percent of column/grand total in my matrix visual - works great drilling up and down a dataset organized into a heirarchy.  However I would like to have a column that adds two or more values in the same row over to a new column, but I don't know how to reference them.  Any idea how to make a column or measure that refers to those percentages and not the underlying values?

 

I have the first 3 columns, want to calculate the 4th and then the 5th.

[Group]  [%CT Enrollments]   [%CT Revenue]  [Points]  [Rank]

A             20%                          15%                   35          2

B             45%                          35%                   80          1

etc.

 

Thank you!

PS Forgive my impromptu table.

 

PPS I realize the math implications of adding/multiplying different averages together.  It's to make a basic scorecard and the percentages are basically becoming "points".  Kind of the math equivilent of rasterizing a vector image.

I have been trying to do this same thing in a bar graph, but the options "percent of row" and "percent of column" do not exist.  Only % of grand total is an option for bar charts.  I tried writing the code as others suggested above, but it did not work for me.  Do you have any suggestions?  Below is a screen shot of the table that I would like to see as a bar graph, showing the percentages that are reflected in each cell of the chart (excluding the total  percentages).

%columns.png

Hi smoupre,

 

Thanks for your reply, i have a stacked bar chart and some values, and as you referred under values area I'm able to find "Show values as" but i can only find 2 options as No Calculation and Percent of grand total but not other options. I have Version: 2.47.4766.801 64-bit (June 2017). Are the other options in latest update?

@mahadeva_raobc,

I am using the latest version of Power BI Desktop(2.48.4792.721 64-bit (July 2017)),“Percent of column Total” is available in Matrix visual.
1.PNG

As you use stacked bar chart and want to show percent of column total, you would need to create a measure. In my environment, I use the DAX below to create the measure and create the bar chart as follows.

Perent of Total =
DIVIDE (
    SUM(Sheet5[SalesAmount]),
    CALCULATE ( SUM ( Sheet5[SalesAmount] ), ALLEXCEPT (Sheet5,Sheet5[Yearofdate]) )
)
2.PNG

If the above step doesn’t help in your scenario, please share sample data of your table and post expected result here.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi,

 

Iam still not getting the expected results i have attached a screenshot of my issue where it has week on left and text on right. i want to display % of text which is on right instead of total numbers.

 

BI.png

@mahadeva_raobc,

Take 201722 as example, please post expected result about it. Also please share sample data of your table so that we can provide you appropriate DAX.

Regards,

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi,

 

Thanks for your reply.

 

Taking 201719 as example chart has numbers 13, 46 ,7, 12.....

I want same chart but % of that column total so for 201729 total is 199 and chart should display 13/199 which is 6.53%

 

So chart should have 6.53% , 23.12%, 3.52% ......instead of 13 ,46 ,7....

@mahadeva_raobc,

Create the following measure in your table.

Perent of Total = 
DIVIDE (
    COUNTA(Sheet1[CIP2]),
    CALCULATE ( COUNTA(Sheet1[CIP2]), ALLEXCEPT (Sheet1,Sheet1[Week]) )
)



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I am facing an issue in matrix 

Purva_Purohit_1-1704352299679.png

i am getting these percent and now i want to show this in stacked column chart so how to write dax query for this result 

 

Purva_Purohit_0-1704352228157.png

 

Thanks v-yuezhe-msft,

 

This resolved my issue..

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors