Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Total SUM of Multiple Values With Filters

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 T...
  • v-huizhn-msft's avatar
    v-huizhn-msft
    8 years ago

    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

v-huizhn-msft's avatar
v-huizhn-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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's avatar
Anonymous
Not applicable
8 years ago

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's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      Perfect! Exactly what I needed. Thank you

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

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