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

 Company Total Revenue % Total Revenue xxx 13 43% xxy 5 17% xxz 5 17% xyx 4 13% xyy 2 7% xyz 1 3%

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

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

Proud to be a Datanaut!

Check out my blog:

Power BI em Português

16 REPLIES 16
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):

Regards,

MFelix

Regards

Miguel Félix

Proud to be a Datanaut!

Check out my blog:

Power BI em Português

Helper II

## Re: Percent of total

This returns an error.

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

Proud to be a Datanaut!

Check out my blog:

Power BI em Português

Helper II

## Re: Percent of total

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

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

Proud to be a Datanaut!

Check out my blog:

Power BI em Português

Helper II

Same table

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

## Re: Percent of total

Did you create a column or a measure?

Regards

Miguel Félix

Proud to be a Datanaut!

Check out my blog:

Power BI em Português

Helper II

## Re: Percent of total

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

Announcements

#### Announcing the New Spanish Forum

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

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

#### ‘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