Power BI – DAX Measures for Excel based “ % of Column Total “ or “ % of Row Total ”
Where I am currently consulting there was a requirement to create a measure like you can in the Excel pivot tables for the % of Column Total or the % of Row Total.
One of the things that you can currently do in Power BI, which I only learnt as part of this requirement is that you can use % of Column Total, or % of Row Total when using a table, as seen below.
But the thing is that as soon as you put that into a visual the only option that you have is the Percent of grand total.
So below are the two measures that I created so that I could then successfully have a % of Column Total (Percent of column total) or % of Row Total (Percent of row total)
DAX – Percent of Row Total
Below is a picture showing how to get the Percent of Row Total, so that for each row it will add up to 100%
Here is the DAX Syntax below, where I did not know that for ALL you could use a table or Column name.
As with the example above I had put the “User Source” on the columns (in a matrix) or Legend (in a Visual)

DAX Syntax
% of User Source on Rows = DIVIDE ( [Sessions], CALCULATE ( [Sessions], ALL ( 'All Web Site Data'[User Source] ) ) )
 In the above the [Sessions] is my Measure
 And on the 3^{rd} line is where I am putting in my column that is going to be in the matrix or Legend in the visual.
 NOTE: If I was looking to use this for some other data, I would then replace the [User Source] column with my data I want to add up to 100% on my rows
So that now when this measure is put into a visual it will all add up to 100%
DAX – Percent of Column Total
Below is a picture showing how to get the Percent of Column Total, so that for each column it will add up to 100%
Here is the DAX Syntax below, and to know what column I wanted to select, it must be the column which will be on your Rows in your table, or the Axis in a Visual.
As with the example above I had put the “Country” on the columns (in a matrix) or Legend (in a Visual)

DAX Syntax
% By Country on Columns = DIVIDE ( [Sessions], CALCULATE ( [Sessions],ALLSELECTED('All Web Site Data'[Country])) )
 In the above the [Sessions] is my Measure
 And on the 3^{rd} line is where I am putting in my column that is going to be in the matrix or Axis in the visual.
 NOTE: If I was looking to use this for some other data, I would then replace the [Country] column with my data I want to add up to 100% on my columns
So that now when this measure is put into a visual it will all add up to 100%. What I did do is to put the Country into the Legend, because this is on Columns for the total to add up to 100%
I hope that this is helpful, in that it can enable you or your organization to leverage your data.
If there are any questions or comments please leave them below.
Can you format the %GT to not include decimals. Example: 100% instead of 100.00%.
Unfortunately it is not currently possible.
Superhelpful! The % or row total was exactly what I needed!
Glad you found it useful
[…] Explanation: References: https://www.fourmoo.com/2017/07/18/powerbidaxmeasuresforexcelbasedofcolumntotalorofrowt… […]
super helpful, but can i keep the row total as numbers.
i’m working on a pivot table where i’m interested in seeing the percentage for every month (rows) from the total. and still wanna see the totals for every row.
Thanks in advance,
Hi there,
Have you tried to use the matrix visual and in the settings change it in the settings to put the measures on Rows?
One solution I found was to use “100% Stacked Column Chart”. It is the 6th Visual on the Visualization Menu. Give it a try.
Hi there
That does work from a visual point of view, but if you want to integrate it with another visual or into a table or matrix this is where the measure comes into its own.
Thank you! I can finally make a 100% stacked area chart with this measure.
Thanks for the feedback, it is great to hear that it worked!
Hello,
I’m currently trying to put this in use to achieve % of column total I’m using a matrix and have 2 fields as rows. When I plug in the row value I want to use into the DAX, it gives me 100% across all. Is using 2 fields as rows, messing it up?
Hi there
It could be that the current measure will not work 100% with the 2 fields causing it to get to 100%.
You could try and modify the DAX measure to get the output you are looking for?
If you get stuck please let me know.
Thanks…I am a very seasoned Power BI professional, and I have looked at a lot of solutions and strategies, and yours was the simplest and very well explained! Thanks very much!!!
Thanks for the kind words, happy to help!
Question: What is the syntax of your [Sessions} measure?
I am not getting result that you achieved with DAX, hence need to revert to %of Column total.
%of Column total only works if rows are filtered. When filters are removed, result is skewed. I suspect it is due to row hierarchy.
The measure [Sessions] is just a SUM of the column.
I would suggest you look at your model and your table to ensure it is working as expected with the base measure?
Hi
Revisiting an old post.
I implemented this solution in a report this week but ran into a “problem”.
When all rows are shown in my matrix all works fine but when I select some rows in a slicer the calculations are adapting to the selection (probably works as designed 🙂 ).
Is there a way to be able to filter the table and still keep the wanted result? Example: Say you filter on Belgium in your matrix, you (or I) still expect 16.67% for edge. When I do similar in my file and my data set it recalculates to 100%. Is there a way around this?
Many thanks
Hi Thomas
Thanks for the question.
And it will be because of the filtering happening when you click on the filter.
If you know what is going to be selected, you might be able to use the ALLSELECTED to see if that resolves your current challenge?
How do you create a percentage for each cell of the grand total? Meaning 6/253 for Argentina Chrome users is 2.37%, or Australia Chrome users is 41.1% (104/253)
Hi there,
What you can do is to calculate the Percentage of total.
Here is an example: DAX Percentage of Total