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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
pdemontigny
Regular Visitor

Calculated Column: % of Total

I'm trying to recreate an Excel dashboard in Power BI. Part of what I need to do is create a table or matrix (not sure which is best) that shows premium by segment, which I've done. Now I want to create another column that calculates the % of total premium for each segment. The end result should look something like this.

 

SegmentInforce PremiumPremium Distribution
A$7,000,00054%
B$4,000,00031%
C$2,000,00015%
Total$13,000,000100%

 

In Excel, I can do this by referencing the cell with the premium for each segment and dividing by the total premium, like this:

 

ColumnBCD
RowSegmentInforce PremiumPremium Distribution
3A7000000=C3/$C$6
4B4000000=C4/$C$6
5C2000000=C5/$C$6
6Total=SUM(C3:C5)=C6/$C$6

 

My problem is I can't figure out how to reference the column total for premium. Does anyone know how I could do this % of total in Power BI?

1 ACCEPTED SOLUTION

So you are saying that you don't want slicer to filter out the total data? In that case, you need to create calculated field.

 

For that you need to create measure, let's say it is called Total Sales,

 

Total Sales = CALCUALTE(SUM(Sales[Revenue]), ALL(Sales))

 

% Revenue = SUM(Sales[Revenue])/[Total Sales]

This is an idea, if you need further help, let me know.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

4 REPLIES 4
Thiyagu
Helper III
Helper III

Hi @pdemontigny,

 

You can achieve by creating a new calculated column with following formula,

 

Premium Dist = DIVIDE(Question[Enforce Premium],SUM(Question[Enforce Premium])).

Query_Power.JPG

 

 Let me know, if you have any queries.

Thanks for your help. This works, however, I want to add a slicer that allows the user to filter for a specific state - meaning the Inforce Premium volumes would change. Do you know how I could set up the formula so that Premium Dist shows the % of total premium for whatever subset of data the user has filtered for?

So you are saying that you don't want slicer to filter out the total data? In that case, you need to create calculated field.

 

For that you need to create measure, let's say it is called Total Sales,

 

Total Sales = CALCUALTE(SUM(Sales[Revenue]), ALL(Sales))

 

% Revenue = SUM(Sales[Revenue])/[Total Sales]

This is an idea, if you need further help, let me know.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

There is inbuild feature in PowerBI to calculate the % of Grand Total.

 

Here is the link to more information https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-may-update-feature-summary/

 

Take a look at Analytic section

 

ANALYTICS

  • Quick Calcs – % of grand total

 

I hope it is helpful.

 

Thanks,

P

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

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

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.