cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

How To Display Percentage of Total

Hi,

 

How can I display the percentage of total next to the settlement? One column is text and the other column is GBP £.

 

PowerBi Query.PNG

Kind regards

 

Dom

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I
Super User I

Re: How To Display Percentage of Total

Hi @DomWest 

 

If Settlement is an explicit measure you can use a DAX expression like this:

% Settlement = 
VAR SettlementVal = [Settlement]
VAR AllSettlement =
CALCULATE (
    [Settlement],
    ALL ( Table1[Channel] )
)
VAR Result = DIVIDE ( SettlementVal, AllSettlement )
RETURN Result

 

If you've simply dropped the Settlement column from your data model and allowed Power BI to aggregate the result (i.e. an implicit measure), then simply add the column to the table again, right click it in the 'Values' field well and select Show value as>>Percent of grant total.

 

Capture.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best regards,

Martyn

 

If I answered your question, please help others by accepting it as a solution.

 

 





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

Proud to be a Super User!




View solution in original post

3 REPLIES 3
Highlighted
Super User IV
Super User IV

Re: How To Display Percentage of Total

Drag the same measure to values in the visual pane. Right click and check do you have the option "Show value AS". If yes click and choose % of Total

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User II
Super User II

Re: How To Display Percentage of Total

Even though it looks like Power BI is adding up a column of numbers here, that is not what is really happening. Power BI calculates each cell individually, based on its filter context.

That means that to get the total amount you need to calculate the percent of total you will have to use a CALCULATE() statement and include the ALL() function. ALL removes the filters that the graphic is generating( to show one line at a time )and restores all the rows in your table so you can total them.

Percent of Total =

VAR Total Amount = CALCULATE(SUM(yourTable[Settlement]),ALL(yourTable))
VAR Current Amount = SELECTEDVALUE(YourTable[Settlement])

VAR PercentOfTotal = DIVIDE([Current Amount],[Total Amount])

RETURN PercentOftotal

I'm a personal Power Bi Trainer I learn something every time I answer a question

The Golden Rules for Power BI

  1. Use a Calendar table. A custom Date tables is preferable to using the automatic date/time handling capabilities of Power BI. https://www.youtube.com/watch?v=FxiAYGbCfAQ
  2. Build your data model as a Star Schema. Creating a star schema in Power BI is the best practice to improve performance and more importantly, to ensure accurate results! https://www.youtube.com/watch?v=1Kilya6aUQw
  3. Use a small set up sample data when developing. When building your measures and calculated columns always use a small amount of sample data so that it will be easier to confirm that you are getting the right numbers.
  4. Store all your intermediate calculations in VARs when you’re writing measures. You can return these intermediate VARs instead of your final result  to check on your steps along the way.



 





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Highlighted
Super User I
Super User I

Re: How To Display Percentage of Total

Hi @DomWest 

 

If Settlement is an explicit measure you can use a DAX expression like this:

% Settlement = 
VAR SettlementVal = [Settlement]
VAR AllSettlement =
CALCULATE (
    [Settlement],
    ALL ( Table1[Channel] )
)
VAR Result = DIVIDE ( SettlementVal, AllSettlement )
RETURN Result

 

If you've simply dropped the Settlement column from your data model and allowed Power BI to aggregate the result (i.e. an implicit measure), then simply add the column to the table again, right click it in the 'Values' field well and select Show value as>>Percent of grant total.

 

Capture.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best regards,

Martyn

 

If I answered your question, please help others by accepting it as a solution.

 

 





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

Proud to be a Super User!




View solution in original post

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors