How To Display Percentage of Total

[Anonymous]

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

[MartynRamsden · Accepted solution]

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]

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.

[amitchandak]

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