Percentages aren't calculating correctly

[GeorgeG]

Hi, I have the below matrix table (cannot show first row, but it is an Account Name with Indiviaul Purchases underneath):

The income percentage formula I used is DIVIDE( Income, Cost ). 

As you can see, they aren't calculating correctly. For example, the third row should show 23.86% but it is returning 28.76%. I have it set to show the Average, as this is the closest result. Does anybody have any idea what's causing this?

Thank you.

[v-rzhou-msft]

Hi @GeorgeG 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[GeorgeG]

Hi,

I found the issue that was causing this. I have a rather large dataset in which the Account Names are connected to multiple figures underneath. It looks like it was calculating the percentage of other figures against the Account Name, to fix this I selected the following options within PowerBI:

[amitchandak]

@GeorgeG , Can you share the formula,

hope it

divide(Sum(Table[Income]),sum(Table[Cost]))

not

average(divide((Table[Income]),(Table[Cost])))

[GeorgeG]

Hi @amitchandak , the formula is as follows:

DIVIDE(
(Table[Income]),

(Table[Cost])

)

If I add SUM to the formula, the below is shown:

 

This is with the Average option selected, either option returns incorrect (If I use the SUM option, it shows over 300% for all rows):

 

Thank you.

[v-rzhou-msft · Accepted solution]

Hi @GeorgeG 

Just like amitchandak has replied, you need to build a measure. And you need if function to make your result see 0.

I build a table like yours.

Measure:

Income Percentage = 
VAR _A = DIVIDE(SUM('Table'[Income]),SUM('Table'[Cost]))
Return
IF(_A=0,0,_A)

Result:

 

You can download the pbix file from this link: Percentages aren't calculating correctly

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[amitchandak]

@GeorgeG , Create a measure like

DIVIDE(
sum(Table[Income]),

sum(Table[Cost])

)