Help with a percentage column

[Richard_Clipsto]

Hi All,

Any help here gratefully received. I have a table which has a column with property names, a column with tenant names, a column with charge types, a column with dates, then columns with amount charged and then amount received.

I would like to show a matrix which shows for a range for dates the amounts charged and received summarised by property - tenant - charge type. So far so good.

I then want to add in a percentage received stat. I can get this to work fine for individual charges, but when I try and add it to my table it will not work. Where there are multiple charges summarised into a single row (what I want) it ends up either adding all the percentages together, or averaging them. I can't get it to add up all the receipts and divided by all the charges. Then for the auto totals per property and for the whole table it either adds them all up or averages the percentages. What I want is each row to show the relevant percentage for all the charges and receipts contained in that row. So I get percentage received for each tenant, for each property, and for the portfolio as a whole.

Thanks!

[saud968]

Summarize Amounts Charged and Received:
Group your data by Property, Tenant, and Charge Type.
Sum the Amount Charged and Amount Received for each group.

Calculate the Percentage Received:
For each group, calculate the percentage received using the formula: [ \text{Percentage Received} = \left( \frac{\text{Total Amount Received}}{\text{Total Amount Charged}} \right) \times 100 ]

Create the Matrix:
Use a pivot table to display the summarized data.
Ensure the pivot table shows the Total Amount Charged, Total Amount Received, and the calculated Percentage Received.
Example in Excel
Assuming your data is in an Excel sheet, here’s how you can set it up:

Summarize Data:
Select your data range.
Go to Insert > PivotTable.
Place the PivotTable in a new worksheet.
Set Up the Pivot Table:
Drag Property to Rows.
Drag Tenant to Rows.
Drag Charge Type to Rows.
Drag Amount Charged to Values (set to Sum).
Drag Amount Received to Values (set to Sum).
Calculate Percentage Received:
In the PivotTable, right-click on the Values area and select Value Field Settings.
Choose Show Values As > % of > Amount Charged.
Add a Calculated Field (if needed):
Go to PivotTable Analyze > Fields, Items, & Sets > Calculated Field.
Name the field Percentage Received.
Use the formula: [ = \left( \frac{\text{Amount Received}}{\text{Amount Charged}} \right) \times 100 ]
Add this calculated field to your PivotTable.
Example in Power BI
If you’re using Power BI, you can achieve this with DAX:

Summarize Data:
Create a new table with summarized data:
SummaryTable =
SUMMARIZE(
YourTable,
YourTable[Property],
YourTable[Tenant],
YourTable[ChargeType],
"TotalAmountCharged", SUM(YourTable[AmountCharged]),
"TotalAmountReceived", SUM(YourTable[AmountReceived])
)

Calculate Percentage Received:
Add a calculated column for the percentage:
PercentageReceived =
DIVIDE(
SummaryTable[TotalAmountReceived],
SummaryTable[TotalAmountCharged],
0
) * 100

Create the Matrix:
Use the Matrix visual.
Add Property, Tenant, and ChargeType to Rows.
Add TotalAmountCharged, TotalAmountReceived, and PercentageReceived to Values.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[_AAndrade]

Hi @Richard_Clipsto,

I'm attaching a pbix file with my solution:

[audreygerred · Accepted solution]

Hello! What do you currently have for your measures? You can try the following:

Total Amount Charged = SUM('YourTable'[YourField_Charged])

Total Amount Received = SUM('YourTable'[YourField_Received])

Total Percentage Received = DIVIDE([Total Amount Received],[Total Amount Charged]

[Richard_Clipsto]

Thank you! I was trying things far more complicated than that!

[audreygerred]

You are very welcome!