Help with a % of Total

[Richard_Clipsto]

Hi,

I am putting together a table of our top 10 tenants and I want to show their % of the total rent for any particular portfolio at any particular month end. A table with top N gives the correct top 10 and works with my slicers to show the right tenants for the selected portfolio/month. But the % of total adds up to 100% and I can't work out how to get this to show their % of the overall total. The overall total though is the % of the total derived from my slicers - for instance the table is:

Month	Tenant	Portfolio	Rent
June 2024	A	X	£100
June 2024	B	X	£200
June 2024	C	X	£250
June 2024	D	Y	£50
June 2024	E	Y	£150
June 2024	F	Y	£200
June 2024	G	Y	£250
July 2024	A	X	£100
July 2024	B	X	£220

Etc.

This is obviously a simplified version but it is basically what I have but lots more tenants and months. So the slicer will be showind data just for portflolio X and June 2024 for instance. and I want to show the top 10 tenants and their % of the total rent for whole of portfolio X in June 2024 (not the % of the top 10).

Many thanks!

[PavanLalwani · Accepted solution]

To display the correct "% of Total" rent for the top 10 tenants in your Power BI table (where each tenant’s percentage is calculated against the total rent of the selected portfolio and month, not just the top 10), you can use a DAX measure that calculates the percentage of the overall total, even with slicers applied. Here’s how:

 Step 1: Calculate the Total Rent for the Selected Portfolio and Month

This measure calculates the total rent for the selected portfolio and month, ignoring the tenant filter to get the overall total for that selection.

```DAX

TotalRentForSelection =

CALCULATE(

    SUM('Table'[Rent]),

    REMOVEFILTERS('Table'[Tenant])

)

```

In this example:

- `'Table'` represents the name of your table.

- `REMOVEFILTERS('Table'[Tenant])` ignores any specific tenant filter applied by the top 10 filter context. This measure will always return the total rent amount for the selected month and portfolio, regardless of the tenants shown.

 Step 2: Create the % of Total Measure

Now, create a second measure to calculate the percentage of the total for each tenant based on the overall rent from the selected portfolio and month.

```DAX

% of Total Rent =

DIVIDE(

    SUM('Table'[Rent]),

    [TotalRentForSelection],

    0

)

```

- This measure calculates each tenant’s rent as a percentage of the overall `TotalRentForSelection`.

- `DIVIDE` handles division by zero gracefully if there is no rent in the selected month/portfolio.

 Step 3: Apply a Top N Filter in Your Visual

In the visual, use the Top N filter to show only the top 10 tenants. The `% of Total Rent` measure should now display each tenant’s rent as a percentage of the total rent in the selected portfolio and month, rather than as a percentage of just the top 10 tenants.

 Optional: Add Visual-Level Filters for Slicers

You can add slicers to select the month and portfolio, so the visual automatically adjusts to the selected criteria and displays the correct total percentage based on the overall rent.

 Summary Table Example

With this setup, your table should look something like this for the selected portfolio (e.g., "X") and month (e.g., "June 2024"):

| Month     | Tenant | Portfolio | Rent | % of Total Rent |

|-----------|--------|-----------|------|-----------------|

| June 2024 | A      | X         | £100 | 10%            |

| June 2024 | B      | X         | £200 | 20%            |

| June 2024 | C      | X         | £250 | 25%            |

Now, the `% of Total Rent` column reflects the percentage based on the overall total rent for the selected portfolio and month, not just the top 10 tenants.

If this solution brightened your path or made things easier, please consider giving kudos. Your recognition not only uplifts those who helped but inspires others to keep contributing for the good of our community!

[Richard_Clipsto]

Thank you to everyone for your suggestions. This one worked - so thank you very much!

[DataNinja777]

Hi @Richard_Clipsto ,

Since the dataset contains fewer than 10 tenants, I’ve added additional data and used the top 5 tenants instead of the top 10 to demonstrate the percentage calculation, ensuring it shows a value less than 100%.

The Top 5 Tenants measure can be written as follows:

Top 5 Tenants = 
VAR TopTenTable = 
    TOPN(
        5, 
        SUMMARIZE(
            'Rent', 
            'Rent'[Tenant], 
            "Total Rent", SUM('Rent'[Rent])
        ), 
        [Total Rent], 
        DESC
    )
RETURN 
    CONCATENATEX(TopTenTable, [Tenant], ", ")

And the Top 5 Tenants' Rent % measure can be written as follows:

% of Total Rent = 
VAR Top3TenantsRent =
    CALCULATE(
        [Total Rent],
        KEEPFILTERS(
            TOPN(5, ALL('Rent'), [Total Rent], DESC)
        )
    )
RETURN
DIVIDE(Top3TenantsRent, [Total Rent])

These can be filtered by your portofolio slicer as shown below:

I hope this meets your required output.  I have attached an example pbix file for your reference.

Best regards,

[Sahir_Maharaj]

Hello @Richard_Clipsto,

Can you please try this approach:

1. Calculate the total rent

TotalRentSelectedContext = 
CALCULATE(
    SUM(Table[Rent]),
    ALL(Table[Tenant])
)

2. Calculate the rent % of total

RentPercentageOfTotal = 
DIVIDE(
    SUM(Table[Rent]),
    [TotalRentSelectedContext],
    0
)

Hope this helps.

[Richard_Clipsto]

This doesn't work as it adds up the rents for all properties irrespective of portfolio and time period.

[ajohnso2]

Total = 
VAR _SelectedTenant = SUM(Rent[Rent])
VAR _SelectedPortfolio = CALCULATE(SUM(Rent[Rent]), FILTER(ALL('Rent'), Rent[Portfolio] = SELECTEDVALUE(Rent[Portfolio])))

RETURN
DIVIDE(_SelectedTenant, _SelectedPortfolio, 0)