cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Snider Frequent Visitor
Frequent 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

Accepted Solutions
Super User
Super User

Re: Total on table from visulizations is not summing everything

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
dilumd Established Member
Established Member

Re: Total on table from visulizations is not summing everything

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.

richbenmintz
Advisor

Re: Total on table from visulizations is not summing everything

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

Snider Frequent Visitor
Frequent Visitor

Re: Total on table from visulizations is not summing everything

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

Snider Frequent Visitor
Frequent Visitor

Re: Total on table from visulizations is not summing everything

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

richbenmintz
Advisor

Re: Total on table from visulizations is not summing everything

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

Snider Frequent Visitor
Frequent Visitor

Re: Total on table from visulizations is not summing everything

😞

 

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.

richbenmintz
Advisor

Re: Total on table from visulizations is not summing everything

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.

Super User
Super User

Re: Total on table from visulizations is not summing everything

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

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 155 members 2,051 guests
Please welcome our newest community members: