cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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 IV
Super User IV

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 IV
Super User IV

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
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors