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

10 REPLIES 10
Greg_Deckler
Super User IV
Super User IV

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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

View solution in original post

Thanks v-yuezhe-msft,

 

This resolved my issue..

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors