Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Niels_T
Post Patron
Post Patron

How to sum up percentages in table

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.

image.png

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]))
1 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:

column.JPG

To calculate the percentages in visuals using a measure:

Measure.JPG

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

11 REPLIES 11
Niels_T
Post Patron
Post Patron

@PaulDBrown @amitchandak 

 

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

@Niels_T 

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






PaulDBrown
Community Champion
Community Champion

@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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






I get the following error:

image.png

 

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 

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]))




Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






image.png


Also doesn't seem to work.

It is based on this data:

image.pngimage.png

 

amitchandak
Super User
Super User

@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 

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:

column.JPG

To calculate the percentages in visuals using a measure:

Measure.JPG

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.