Percent of column Total

[mahadeva_raobc]

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

[Greg_Deckler]

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.

[TylerBrown]

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.

[annerogers]

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

[mahadeva_raobc]

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?

[v-yuezhe-msft]

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

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

[mahadeva_raobc]

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.

[v-yuezhe-msft]

@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,

[mahadeva_raobc]

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

[v-yuezhe-msft · 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

[Purva_Purohit]

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 

[mahadeva_raobc]

Thanks v-yuezhe-msft,

This resolved my issue..