Forum Discussion

Richard_Clipsto's avatar
Richard_Clipsto
Frequent Visitor
1 year ago
Solved

Help with a percentage column

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!

Need Help
  • audreygerred's avatar
    audreygerred
    1 year ago

    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]

5 Replies

  • audreygerred's avatar
    audreygerred
    Icon for Super User rankSuper User
    1 year ago

    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]

  • saud968's avatar
    saud968
    Icon for Memorable Member rankMemorable Member
    1 year ago


    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!