cancel
Showing results 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

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
Employee

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.
11 REPLIES 11
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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

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.

Helper I

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

Frequent Visitor

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?

Employee

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.

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

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.
Frequent Visitor

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.

Employee

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.
Frequent Visitor

Hi,

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....

Employee

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.
New Member

I am facing an issue in matrix

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

Frequent Visitor

Thanks

This resolved my issue..

Announcements

#### 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 Monthly Update - June 2024

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

#### 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
Top Kudoed Authors