How to sum up percentages in table

[Niels_T]

Hello,

I have a table that I want to make with 3 columns: Sales, Margin and Margin %.

However this percentage is still not correct. What should I do to just get the exact percentage for each line? In my data it is correct for each seperate line but when counted together in a table it gives me a value that is slightly off.

In this case the margin 13,95 % should be 14,04 %.

My measure for percentage: 

('xxxxNV$Sales Invoice + Cr_Memo Line'[Local Currency Amount] - ('xxxxNV$Sales Invoice + Cr_Memo Line'[Unit Cost (LCY)] * 'xxxxNV$Sales Invoice + Cr_Memo Line'[Quantity (Base)]))/ ('xxxxNV$Sales Invoice + Cr_Memo Line'[Local Currency Amount]))

[Niels_T]

@PaulDBrown @amitchandak 

I want to upload a sample .pbix file how can I upload it? It says .pbix not supported.

[PaulDBrown]

@Niels_T 

You can upload it to a service like Onedrive, Google Drive, Dropbox and shre from there

[Niels_T]

https://www.dropbox.com/s/q5fd4wxb933ld7z/Sample%20File.pbix?dl=0

Here you go!

[PaulDBrown]

@Niels_T 

Assuming the rows in the table visual are from the table 'TABLE', try:

% = SUMX('TABLE', 
DIVIDE('xxxxNV$Sales Invoice + Cr_Memo Line'[Local Currency Amount] - ('xxxxNV$Sales Invoice + Cr_Memo Line'[Unit Cost (LCY)] * 'xxxxNV$Sales Invoice + Cr_Memo Line'[Quantity (Base)])), ('xxxxNV$Sales Invoice + Cr_Memo Line'[Local Currency Amount]))

Change TABLE for whatever table is the filter context in the visual

[Niels_T]

I get the following error:

SUMX('xxxxNV$Sales Invoice + Cr_Memo Line',

DIVIDE('xxxxNV$Sales Invoice + Cr_Memo Line'[Local Currency Amount] - ('xxxx$Sales Invoice + Cr_Memo Line'[Unit Cost (LCY)] * 'xxxxNV$Sales Invoice + Cr_Memo Line'[Quantity (Base)])), ('xxxxNV$Sales Invoice + Cr_Memo Line'[Local Currency Amount]))

[PaulDBrown]

@Niels_T 

There is a bracket too many. Try:

SUMX('xxxxNV$Sales Invoice + Cr_Memo Line',

DIVIDE('xxxxNV$Sales Invoice + Cr_Memo Line'[Local Currency Amount] - ('xxxx$Sales Invoice + Cr_Memo Line'[Unit Cost (LCY)] * 'xxxxNV$Sales Invoice + Cr_Memo Line'[Quantity (Base)]), ('xxxxNV$Sales Invoice + Cr_Memo Line'[Local Currency Amount]))

[Niels_T]

Also doesn't seem to work.

It is based on this data:

[amitchandak]

@Niels_T , Looking at this; it seems calculation is wrong. But I would like see both number with more decimal places, to make sure the calculation has some problem .

Also have you used round funtion somewhere in calculations.

Check with more decimal place and remove round used in any calculations (round function, not the measure format)

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Niels_T]

https://www.dropbox.com/s/q5fd4wxb933ld7z/Sample%20File.pbix?dl=0

[PaulDBrown · Accepted solution]

@Niels_T 

Thanks for the sample PBIX. It makes life much easier.

As regards the problem... Creating these type of % as columns in data tables doesn´t really make much sense. IT's much better to use measures, since normally you will be summing values and the & calculation is for aggregations is done on the totals. so...

If you still want to keep the % margin as a column:

To calculate the percentages in visuals using a measure:

[Niels_T]

I will try to make some sample data. 

What do you mean both numbers with more decimal places?

The complete function is: 

Margin % =

IF(RELATED('xxxxNV$Sales Invoice + Cr_Memo Header'[Document Type]) = "Credit Memo",

0,

('xxxxNV$Sales Invoice + Cr_Memo Line'[Local Currency Amount] - ('xxxxNV$Sales Invoice + Cr_Memo Line'[Unit Cost (LCY)] * 'xxxxNV$Sales Invoice + Cr_Memo Line'[Quantity (Base)]))/ ('xxxxNV$Sales Invoice + Cr_Memo Line'[Local Currency Amount]))

The if statement is to filter out the credit memo's.