cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KGuyton Helper II
Helper II

Percent of total

I am trying to display a value for the percent of a column total.  Ive run into two problems.

1. Tried creating a new column that housed the column total for each row but that total doesn't change when slicers are used therefore as the column amounts get lower, the total stays the same resulting in much smaller than actual percentages.

2. I cannot use a matrix and show "percent of grand total" because I am filtering the top N results.  I need a percentage based on all rows even though only the top 15 are being shown.

 

My table needs to look like this: 

CompanyTotal Revenue% Total Revenue
xxx1343%
xxy517%
xxz517%
xyx413%
xyy27%
xyz13%

 

But if I choose to show only the top 3 then total needs to remain a sum of all companies.  Same goes for adjusting the date, the revenue will be smaller so the total needs to recalculate based on the new filtered data.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: Percent of total

My solution is based on a measure.

 

Can you share a sample file? If there is sensitive information you can share in private message.

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Datanaut!

Check out my blog:

Power BI em Português





View solution in original post

16 REPLIES 16
Super User III
Super User III

Re: Percent of total

Hi @KGuyton ,

 

You need to create the following measure:

 

% Revenue = SUM(Revenue[Total Revenue])/CALCULATE(SUM(Revenue[Total Revenue]);ALL(Revenue[Company]))

Then you can use the top N to filter out your table as you can see the line values keep the same and the total sum the percentage of the selected lines (top table is for comparision):

totalpercentage.gif

Regards,

MFelix

 


Regards

Miguel Félix


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

Proud to be a Datanaut!

Check out my blog:

Power BI em Português





KGuyton Helper II
Helper II

Re: Percent of total

This returns an error.

Untitled.png

Super User III
Super User III

Re: Percent of total

Hi @KGuyton ,

 

Try replacing the dot comma by comma believe that as to do with regional settings.

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Datanaut!

Check out my blog:

Power BI em Português





KGuyton Helper II
Helper II

Re: Percent of total

No errors but the data only returns a value of 1.00 for each row

Super User III
Super User III

Re: Percent of total

Hi @KGuyton,

 

Is you company on the same table as your revenue or are they on different tables?

 

Can you share a sample file?

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Datanaut!

Check out my blog:

Power BI em Português





KGuyton Helper II
Helper II

Re: Percent of total

Same table

KGuyton Helper II
Helper II

Re: Percent of total

Anybody else?  I can't believe such a worthwhile calculation is this hard to display in PBI.

Super User III
Super User III

Re: Percent of total

Did you create a column or a measure?

Regards

Miguel Félix


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

Proud to be a Datanaut!

Check out my blog:

Power BI em Português





KGuyton Helper II
Helper II

Re: Percent of total

I tried a measure first then a column.  Neither have worked out so far.

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors