How to calculate the total from the sum of items in the same column

[Carludo]

Hi,

I have a column with the Funds for clients which can be Expense or Income; how do I calculate the Funds percent to display the expense as a percentage of the full income which in the example below is 3530.28

For instance the first expense should be 11.34% instead of the 14.70% below. 

The formula I am using is showing me the percentage per row per group instead; below is the formula I found in a forum.

Funds Percent = sum('HCP Statement Data'[Funds])/CALCULATE(sum('HCP Statement Data'[Funds]),FILTER(ALLSELECTED('HCP Statement Data'),'HCP Statement Data'[Expenses type]=SELECTEDVALUE('HCP Statement Data'[Expenses type])))

Thanks for your help

[Carludo]

I would be happy with the -11.34% too since it is techically a deduction from the sum of Income. I just cant get that elusive 11.34% happening or any of the Expense entries to be divided by the full Income

[lbendlin]

Funds Percent = divide(sum('HCP Statement Data'[Funds]),CALCULATE(sum('HCP Statement Data'[Funds]),'HCP Statement Data'[Expenses type]="Income",REMOVEFILTERS('HCP Statement Data'[Index])))

[Carludo]

Hi Ibendlin,

I am getting the following error with that formula: "Column 'Index' in table 'HCP Statement Data' cannot be found or may not be used in this expression"

If I replace Index with "Expenses Type" to remove the filter I get the following: 

The good news is that is calculating the % of the remaining funds out of the total income instead of displaying Infinity as it was before

[lbendlin]

In Power Query add an index column. Include it in the visual.

[Carludo]

I created the Index in the query, reloaded the data and added it to the table but it is still only displaying the PErcentage for the Income items

[Irwan · Accepted solution]

hello @Carludo 

i assume [Sum of Funds] is a measure.

Next, you need to calculate [Sum of Funds] inside your table with "Income" as filter to get full income value.

After that, you can just divide those two measure to get [Funds Percent].

Funds Percent = 
var _Total = CALCULATE([Sum of Funds],ALL('Table'),'Table'[Type]="Income")
Return
DIVIDE(
    [Sum of Funds],
    _Total
)

note: ignore the 'No' column since you are most likely have another column to differentiate those expense and income, it just there to keep those rows separated otherwise it will be summarized when put in table visual.

Hope this will help.

Thank you.

[Carludo]

Hi Irwan,

Thank you for the guidance; Funds is a column on the dataset but with your formula I was able to do some small changes and use ALLSELECTED instead of ALL and got the correct figures

[Irwan]

hello @Carludo 

glad to see it works.

Thank you.

[lbendlin]

Why not -11.34% ?