Total SUM of Multiple Values With Filters

[Anonymous]

Hello,

I have multiple columns and values that I want a mesure to SUM into one table. Below is an image of 3 tables with information such as this:

Rows: 1 QC Artist

Values: Linear KM, 1 QC Time (hr)

The problem is that 1 QC Artist Row 1 <name> might not be the same as 2 QC Artist Row 1 <name>

What it needs to do is

(SUM the Linear KM based on 1 QC Artist + SUM the Linear KM based on 2 QC Artist + SUM the Linear KM based on 3 QC Artist)

'Alejandro' - 100.59 KM - 112.53 (hr)

'Ben' - 254.74 KM - 96.96 (hr)

etc.

 thank you

[v-huizhn-msft]

Hi @Anonymous,

Based on my unserstanding, the three table visualizations are created from three different resource table, right? If it is, I personally suggest you create a new table to combine three resource tables. Then create a table visual to display the name and SUM the Linear KM based on 1 QC Artist + SUM the Linear KM based on 2 QC Artist + SUM the Linear KM based on 3 QC Artist.

I assume the "1 QC Artist", "Linear KM", "1 QC Time (hr)" come from Table1, "2 QC Artist", "Linear KM", "2 QC Time (hr)" come from Table2, and "3 QC Artist", "Linear KM", "3 QC Time (hr)" come from Table3. First, please create a new table including the summarized Table1, Table2 and Table3 using the formula below after clicking "New Table" under "Modeling" on Home page.

NewTable =
UNION (
    SUMMARIZE (
        Table1,
        Table1[1 QC Artist],
        "Linear KM", SUM ( Table[Linear KM] ),
        "QC Time (hr)", SUM ( Table1[1 QC Time (hr)] )
    ),
    SUMMARIZE (
        Table2,
        Table2[2 QC Artist],
        "Linear KM", SUM ( Table2[Linear KM] ),
        "QC Time (hr)", SUM ( Table2[2 QC Time (hr)] )
    ),
    SUMMARIZE (
        Table3,
        Table3[3 QC Artist],
        "Linear KM", SUM ( Table3[Linear KM] ),
        "QC Time (hr)", SUM ( Table3[3 QC Time (hr)] )
    )
)

Then create a measure to get the total sum using the formula: 

Total_Linear =
SUM ( NewTable[Linear KM] )

Total_Time =
SUM ( NewTable[QC Time (hr)] )

Finally, create a table visual to display the result, select the "1 OC Artist" and measure [Total_Linear] and [Total_Time] fields as value levels.

Best Regards,
Angelia

[Anonymous]

Actually all of the information is on the same table, just under their own columns, so I don't think that this solution works. My problem might not be solvable with a simple measure.

I want the following total for every user:

=SUMIF([Linear KM:Linear KM], [1 QC Artist]:[1 QC Artist], "Andrew") + SUMIF([Linear KM:Linear KM], [2 QC Artist]:[2 QC Artist], "Andrew") + SUMIF([Linear KM:Linear KM], [3 QC Artist]:[3 QC Artist], "Andrew")

[v-huizhn-msft]

Hi @Anonymous,

Please share your table structure or sample table, so that we can post solution which is close to your requirement.

Best Regards,
Angelia

[Anonymous]

I forgot to include it in the image, but each row also has it's own linear KM count

I also have this, which works for a specified user, but I don't want to have to do that.

QC Total Measure = 
    CALCULATE(
		SUM( 'Daimler A1 Round Trip Ulm LOs'[Linear KM]),
			FILTER( 'Daimler A1 Round Trip Ulm LOs','Daimler A1 Round Trip Ulm LOs'[1 QC Artist] = "Alejandro"))
	+ CALCULATE( 
		SUM( 'Daimler A1 Round Trip Ulm LOs'[Linear KM]),
			FILTER( 'Daimler A1 Round Trip Ulm LOs','Daimler A1 Round Trip Ulm LOs'[2 QC Artist] = "Alejandro"))
	+ CALCULATE( 
		SUM( 'Daimler A1 Round Trip Ulm LOs'[Linear KM]),
			FILTER( 'Daimler A1 Round Trip Ulm LOs','Daimler A1 Round Trip Ulm LOs'[3 QC Artist] = "Alejandro"))

[v-huizhn-msft · Accepted solution]

Hi @Anonymous,

Please create a new table using the following formula, which combine the different columns to one columns.

NewTable =
UNION (
    SELECTCOLUMNS (
        Table,
        "QC Artist", Table[1 QC Artist],
        "Linear KM", Table[Linear KM],
        "QC time", Table[1QC Time(min)]
    ),
    SELECTCOLUMNS (
        Table,
        "QC Artist", Table[2 QC Artist],
        "Linear KM", Table[Linear KM],
        "QC time", Table[2QC Time(min)]
    ),
    SELECTCOLUMNS (
        Table,
        "QC Artist", Table[3 QC Artist],
        "Linear KM", Table[Linear KM],
        "QC time", Table[3QC Time(min)]
    )
)

Then create measure like the original post, and display it in table.

Total_Linear =
SUM ( NewTable[Linear KM] )

Total_Time =
SUM ( NewTable[QC Time (hr)] )

Best Regards,
Angelia

[Anonymous]

@v-huizhn-msft Apologies, I know this is already solved, but how could I do this while filtering out blank values? 

[Anonymous]

Perfect! Exactly what I needed. Thank you