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
Snider
Regular Visitor

Total on table from visulizations is not summing everything

I have a table that list the sum of costs incurred during repairs of motors.  Some of the line items are incurred multiple times in the table, but are for different jobs (same description/cost for different jobs).  The issue that I am running into is that the total is not summing everything for an overall total. 

 

I have exported the data and removed duplicates on a combination of two columns which revealed that the total is summing the first occurence of the combination not every line item.  The sum of every line item should be $51,837, but the table is totaling it to be $36,984.  Below are the tables.  Table 1 is every line item that has the total of $51,837.  Table 2 is the duplicate removed that has the total of $36,984.

 

How do I get the built in total function of the table from the visualizations to sum every line item?

 

Table 1

QA/QC Odd-Lobe Rotor_Disposition of Part_0$5,750
QA/QC Even-Lobe Rotor_Disposition of Part_0$5,750
QA/QC 6.5 Stator_Disposition of Part_0$4,780
QA/QC 6.5 Stator_Disposition of Part_0$4,780
QA/QC Stabilizer/Kick Pad Sleeve_Disposition of Part_0$2,500
QA/QC Fixed Bend Housing_Disposition of Part_0$2,400
QA/QC Flex Shaft Extension Housing_Disposition of Part_0$1,938
QA/QC Bearing Mandrel_Disposition of Part_0$1,800
QA/QC Bearing Mandrel_Disposition of Part_0$1,800
QA/QC Lower Outer Radial Bearing_Disposition of Part_0$1,645
QA/QC Upper Outer Radial Bearing_Disposition of Part_0$1,645
QA/QC Lower Outer Radial Bearing_Disposition of Part_0$1,645
QA/QC Upper Outer Radial Bearing_Disposition of Part_0$1,645
QA/QC Lower Inner Radial Bearing_Disposition of Part_0$1,580
QA/QC Lower Inner Radial Bearing_Disposition of Part_0$1,580
QA/QC Upper Inner Radial Bearing - Flow Diverter_Disposition of Part_0$1,500
QA/QC Upper Inner Radial Bearing - Flow Diverter_Disposition of Part_0$1,500
QA/QC Bearing Housing with External Threads_Disposition of Part_0$1,461
QA/QC Bearing Housing - Slick_Disposition of Part_0$1,151
QA/QC Outer PDC Race_Disposition of Part_0$1,030
QA/QC Inner PDC Race_Disposition of Part_0$1,015
QA/QC Inner PDC Race_Disposition of Part_0$1,015
QA/QC End Nut_Disposition of Part_0$439
QA/QC End Nut_Disposition of Part_0$439
QA/QC Rotor Coupling_Disposition of Part_0$397
QA/QC Rotor Coupling_Disposition of Part_0$397
QA/QC Outer Bearing Races_Select DBR if any races are scrapped_0$95
QA/QC Inner Bearing Races_Select DBR if any races are scrapped_0$56
QA/QC Outer PDC Spacer_Disposition of Part_0$30
QA/QC Outer PDC Spacer_Disposition of Part_0$30
QA/QC Inner PDC Spacer_Disposition of Part_0$22
QA/QC Inner PDC Spacer_Disposition of Part_0$22

 

 

Table 2

QA/QC Odd-Lobe Rotor_Disposition of Part_0$5,750
QA/QC Even-Lobe Rotor_Disposition of Part_0$5,750
QA/QC 6.5 Stator_Disposition of Part_0$4,780
QA/QC Stabilizer/Kick Pad Sleeve_Disposition of Part_0$2,500
QA/QC Fixed Bend Housing_Disposition of Part_0$2,400
QA/QC Flex Shaft Extension Housing_Disposition of Part_0$1,938
QA/QC Bearing Mandrel_Disposition of Part_0$1,800
QA/QC Lower Outer Radial Bearing_Disposition of Part_0$1,645
QA/QC Upper Outer Radial Bearing_Disposition of Part_0$1,645
QA/QC Lower Inner Radial Bearing_Disposition of Part_0$1,580
QA/QC Upper Inner Radial Bearing - Flow Diverter_Disposition of Part_0$1,500
QA/QC Bearing Housing with External Threads_Disposition of Part_0$1,461
QA/QC Bearing Housing - Slick_Disposition of Part_0$1,151
QA/QC Outer PDC Race_Disposition of Part_0$1,030
QA/QC Inner PDC Race_Disposition of Part_0$1,015
QA/QC End Nut_Disposition of Part_0$439
QA/QC Rotor Coupling_Disposition of Part_0$397
QA/QC Outer Bearing Races_Select DBR if any races are scrapped_0$95
QA/QC Inner Bearing Races_Select DBR if any races are scrapped_0$56
QA/QC Outer PDC Spacer_Disposition of Part_0$30
QA/QC Inner PDC Spacer_Disposition of Part_0$22
1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi @Snider,

 

You do not need to remove duplicates at all.  From the first table, drag Part (column1) to the visual and write this calculated field formula

 

=if(HASONEVALUE(Data[Part]),MIN(Data[Cost]),SUMX(SUMMARIZE(Data,[Part],"ABCD",SUM(Data[Cost])),[ABCD]))

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

8 REPLIES 8
Ashish_Mathur
Super User
Super User

Hi @Snider,

 

You do not need to remove duplicates at all.  From the first table, drag Part (column1) to the visual and write this calculated field formula

 

=if(HASONEVALUE(Data[Part]),MIN(Data[Cost]),SUMX(SUMMARIZE(Data,[Part],"ABCD",SUM(Data[Cost])),[ABCD]))

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
richbenmintz
Solution Sage
Solution Sage

Can you share the expression you are using to calculate the measure and the pbix file, would be pretty easy to see the problem with the dataset



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


Creating a new measure will not solve the issue I am having.  Creating a new measure even if I use sum or sumx creates another column which is still totaled incorrectly by the visualization table tool.

 

How do I get the total at the bottom of the table to add everything not just the first occurence of it?  Below is the table I need to have the total corrected on.

 

Please help.

 

2017-11-02_15-13-43.png

dilumd
Solution Supplier
Solution Supplier

Hi

 

I'm not very clear about your requirement, however if you create a new measure with SUM function you should be able to get the total amount.

Creating a new measure will not solve the issue I am having.  Creating a new measure even if I use sum or sumx creates another column which is still totaled incorrectly by the visualization table tool.

 

How do I get the total at the bottom of the table to add everything not just the first occurence of it?  Below is the table I need to have the total corrected on.

 

Please help.

 

2017-11-02_15-13-43.png

please provide a sample file, without seeing your data I cannot say what the problem is, it could be your relationships, it could the measure definition.

 

Sorry



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


😞

 

The issue is that I dont have a measurement involved and the everything is working correctly relationship wise. The problem is the built in total function of the table and not having the ability to change it or see why it is summing what it is.

Hi @Snider,

 

I am sure there is something that is not quite correct. I understand if you cannot share your pbix, but at this point i would need it to troubleshoot, i have never had this occur in all my experience with the tool.



I hope this helps,
Richard

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Proud to be a Super User!


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.