cancel
Showing results for
Did you mean:
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
Microsoft

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.
10 REPLIES 10
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!:
Learn Power BI 2nd Edition
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?

Microsoft

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.

Microsoft

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

Microsoft

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

Thanks

This resolved my issue..

Announcements

#### The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

#### Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!