Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to Solution.
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]))
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]))
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
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.
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.
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
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.
Proud to be a Super User!
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |