% Spend on "Objects of Trust" using Excel DAX Pivot Table - an important parameter for evaluating every NGO or Trust

% Spend on "Objects of Trust" - A quick analysis using pivot table and measures for decision making  

Let us say we have two- or three-years of financial statements from an #NGO or a #Trust serving a social sector. Let us analyse expenses and incomes separately.

When it comes to expenses, we want to analyse the expenses on “Objects of the Trust” as a percentage of the total expenses. “Objects of the Trust” are the focus areas e.g. education, health care. Ideally most of the money should be spent on its focus areas by every NGO or Trust.

Let us consider the following ways amongst others.

1. Using pencil, paper and a calculator.

2. Using a spreadsheet and putting expressions to get percentages of the total expenses. It is not very difficult. But in case you do it frequently or in case you have a lot of rows of transactions; a pivot table may offer a better option. The Pivot Table also offers flexibility in analysing data.

3. Calculating percentages of column total using pivot tables and measures

Let us try the last option of pivot tables and measures as defined by Microsoft.

Let us start with the data. I have converted the range into a table. There are many benefits of using a table in Excel.

Input data to be analysed using excel DAX and private tables

“I” refers to income and “E” to expenses. “Obj” refers to Object of the Trust. 

Then copy the pivot table to the data model.

Later we will develop measures to calculate percent (of every row item) of column total. It is always a good idea to use explicit measures rather than the built-in implicit measures in pivot tables.

Pivot Table: Please add to the DataModel.

An Excel Table added to Data Model for analysis in Pivot Table

The table (range) is named DDDrange.

When you are building the pivot table, please use the tabular report layout because it names columns headings.

Let us build the measures. You can add a measure by right clicking the table DDDrange. 

An Excel Pivot Table being made using measures

Here is the first measure. The DAX expression would be as follows.

Adding a measure - How to calculate a measure in DAX in Excel

Here is the second measure.

Adding a second a measure in DAX in Excel

The final measure is the ratio of the earlier two measures.

Adding a final measure in DAX in Excel

We have got what we wanted.

Our calculated Pivot Table is now ready

NGOs that spend a high percentage (above 85%) on “Objects of the NGO or Trust” are preferred.  

Bonus Tip - Excel Pivot Table with conditional formatting:

Using conditional formatting, we can easily see that this particular NGO or Trust has spent more than 85% of its spend on Objects of the Trust. It can be easily spotted by green circles. The process cold be used to analyse many more financial statements from different NGOs and without any errors. You and your manger will be happy.

Our calculated Pivot Table with conditional formatting for ease of understanding