Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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 Super User!

Check out my blog: Power BI em Português



View solution in original post

16 REPLIES 16
v-juanli-msft
Community Support
Community Support

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]

Capture2.JPGCapture3.JPG

 

 

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.

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

Untitled.png

Hi @KGuyton 

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

As tested, my result is as below:

Capture10.JPG

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.

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.

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.

Capture6.JPG

Capture7.JPG

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
Helper II
Helper II

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

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 Super User!

Check out my blog: Power BI em Português



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

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 Super User!

Check out my blog: Power BI em Português



Pm'd

MFelix
Super User
Super User

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 Super User!

Check out my blog: Power BI em Português



This returns an error.

Untitled.png

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 Super User!

Check out my blog: Power BI em Português



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

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 Super User!

Check out my blog: Power BI em Português



Same table

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.