Percent of Total Income

[tracycrown]

Dear sir/madam

Can someone advise why I cannot compute % of Total Income

eg. Expenditure as % of Total Income etc.

Thank you, Tracy

[rubenmarin · Accepted solution]

Hi, 'Total Income ' has en empty space at the end, and instead of 'if', using set analysis could give a better performance. And lastly, if you have a dimension in a table that is related to account, you will need to use TOTAL to ignore that dimension filter, and adding <Year> to not ignore the year dimension.

The final expression could be:

Column(1)/sum(TOTAL <Year> {<Account={'Total Income '}>} Data)

[tracycrown]

Hi Mr Rubenmarin

Thank you so much for your advice.

Could you please help again as the percentage is incorrect, Total income should be 100%, not 200% in Year 2019. I tried to take out "Total" in the script but also wrong,

Thanks, Tracy

[Fabiano_Martino_Intelco · Accepted solution]

Hello @tracycrown ,

with your data the 200% value is correct, because in the Account field you have the "Total Income" element that summarizes all the others Account in the "Income" AcctGrp.

The save is also true for the "Expenditure" AcctGrp, where you have the "Total Expenditure" Account.

I suggest you filter those two Accounts with a Set Expression.

Regards

Fabiano

[rubenmarin · Accepted solution]

Hi, Total Income for 2019 is 5345.44, so the percentage of Income over 'Total Income' is that 200%.

If you want to avoid that you need to change the relationship between Income and Total Income to avoid that Total Income is included in the 'Income' group, which causes the duplciated value of Income.

You can try calculating the amount as: =sum({<Account={"=Len(AcctType)>0"}>} Data)

[tracycrown · Accepted solution]

Hi Mr Rubenmarin

Very sorry for my mistake in column(1), I have made changes according to your advice and it works now in Table 1. 

May you also advise why I could not sort "Account" in Load Order (ie Total Income should be displayed at the bottom, same as Excel format) in Table 2.

Many Thanks, Tracy

[tracycrown · Accepted solution]

Hi Mr Fabiano_Martino_Intelco

Thank you so much for your kind help and observation, it was my mistake in column(1) and not the 200%.

Many Thanks, Tracy

[rubenmarin · Accepted solution]

Hi Tracy, Load Order it's not allowed because the dimension is a calculated dimension, there could be another solutions making some changes in data model, or setting the filter as set analysis in expression.

Keeping everything as it is you can try setting the sort expression as FieldIndex('Account',Account)

[tracycrown]

Hi Mr. Rubenmarin

Yes, the sorting issue has been resolved for "Income" but not for "Expenditure" (Eg. Accounting Service Fee should be the 1st in Load Order). Can you suggest a better way or changing the data format or wording in Excel data source so that % of Total Income and Sorting can be done easily. 

Many Thanks, Tracy

[rubenmarin]

Hi, you can add a table withh Account as dimension and FieldIndex('Account',Account) as expression, so you can check the load order of the values.

Maybe Donations has already been loaded as an income, and when the same 'donations' appears as expenditures, it has been already loaded, so it keeps the index it has when it was loaded as an income.

If that's the case you can avoid it crating a sorting field that loads each account as an 'I/E' (one leeter to identify Income or Expenditure) and the account, the sorting by FieldIndex('Account',[I/E] & Account)