Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 £.   Kind regards   Dom
Need Help
  • MartynRamsden's avatar
    MartynRamsden
    6 years ago

    Hi Anonymous 

     

    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.

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best regards,

    Martyn

     

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

     

     

kentyler's avatar
kentyler
Icon for Solution Sage rankSolution Sage
6 years ago

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.