Calculating Percentages of Totals and Row level correctly

[aditya_kamdi]

I have been trying to calculate the percentage for Sales achieved in " Target%". However, I can either get the percentages to calculate accurately either at every row-level or at the grand total correctly.

I have two values 'Net Sales This Year'[Net Amount] and 'Project Details'[Hong Kong Target Calculation.amount] and the Percentage can be obtained by dividing  'Net Sales This Year' with [Hong Kong Target Calculation.amount]. There are 2 summary levels one is project and other is a state. 

If I use RELATED I get it right at Project Level i.e the lowest level of summary and not at the state and grand total level 

Formula: 

Target % = DIVIDE('Net Sales This Year'[Net Amount], RELATED('Project Details'[Hong Kong Target Calculation.amount]), 0 )

If I use SUMX I get the Grand Total right but the summary levels are just wrong

Formula: 

Target % =

DIVIDE('Net Sales This Year'[Net Amount], SUMX('Project Details','Project Details'[Hong Kong Target Calculation.amount]), 0 )

[dax · Accepted solution]

Hi aditya_kamdi,

It seems that you use calculated column in Matrix, right? I am not sure your data structure, you could try to use measure like below

SUM('Table'[amount])/SUM(t2[target])

to see whether it work or not. In addition, if your data structure is not similar to my and the measure doesn't work, please inform me more detailed information(such as your sample data and your expected output)? Then I will help you more correctly.

Best Regards,
Zoe Zhi

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