Percent of total

[KGuyton]

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.

[v-juanli-msft]

Hi @KGuyton 

Create measures

Measure = CALCULATE(SUM('Table'[Total Revenue]),ALL('Table'))

Measure 2 = CALCULATE(SUM('Table'[Total Revenue]),ALLEXCEPT('Table','Table'[Company]))

Measure 3 = [Measure 2]/[Measure]

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KGuyton]

Close but the percentages are off for some reason.  Total revenue for that time period is 11.14M.  Line 4 should be closer to 12% but your measure shows 3.49%.

[v-juanli-msft]

Hi @KGuyton 

I don't know how you get the measure "Total%"?

As tested, my result is as below:

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KGuyton]

you are only showing the percentage based on those 20 companies.  My goal is to show the percentage based on all the companies even when the table is set to show only the top 20 companies based on revenue.  The top 20 companies will not make up 100% of the revenue. 

Pretend you have a table in excel with all companies sorted by revenue and their respective percentages.  Then you hide all companies except the top 20, the percentages should not change.

[v-juanli-msft]

Hi @KGuyton 

There may be some misunderstanding.

The [total] measure is the total regardless of the top 20, its the total of all revenues for all companies.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KGuyton]

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

[MFelix]

Did you create a column or a measure?

[KGuyton]

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

[MFelix · Accepted solution]

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

[KGuyton]

Pm'd

[MFelix]

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

[KGuyton]

This returns an error.

[MFelix]

Hi @KGuyton ,

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

Regards,

MFelix

[KGuyton]

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

[MFelix]

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

[KGuyton]

Same table