Forum Discussion

bolabuga's avatar
bolabuga
Helper V
9 years ago
Solved

Calculation between columns on a table

Good day.

 

i have a Table with 1.700.000 rows for the columns "econres", "categoria", "consume".

 

Categoria column has 5 values (R,I,P,C,M).

 

I need to do: sum "econres" if "categoria" = "R" and sum "consume" if "categoria" = "R", then divide the resulted sum of "consume" and "econres", because i need the value (consume per econres on the categoria "R")

 

Thks

  • Anonymous's avatar
    Anonymous
    9 years ago

    Two ways to do this. First, if you only need this for categoria "R" then you can explicitly write the measure to only use that:

     

    consume per econres categoria R = CALCULATE(
    	DIVIDE(
    		SUM(TableName[consume]),
    		SUM(TableName[econres])
    	),
    	FILTER(
    		TableName,
    		TableName[categoria] = "R"
    	)
    )

    The second method would apply if you also want to do the same calculation for categorias I, P, C, and M. In that case you can simply write the basic form of the above measure:

     

    consume per econres = DIVIDE(
    	SUM(TableName[consume]),
    	SUM(TableName[econres])
    )

    ...and plot that measure against the categoria column (that is, use categoria as the rows in a matrix, or the legend or X axis in some chart). The context from that column will make the measure give you each categoria, behaving just like the filter statement in my first example.

  • Anonymous's avatar
    Anonymous
    9 years ago

    bolabuga you can have more than one filter and more than one condition per filter.

     

    If you want to filter based on more than one column in the same table:

     

    consume per econres categoria R = CALCULATE(
    	DIVIDE(
    		SUM(TableName[consume]),
    		SUM(TableName[econres])
    	),
    	FILTER(
    		TableName,
    		TableName[categoria] = "R" &&
    		TableName[OtherColumn] = "X" 
    	)
    )

     && is "and", meaning both conditions must be met. Substitute that with || if you want to do an "or" condition. You can string together as many conditions as you like this way.

     

    If you have two tables connected by a relationship, and you want to filter based on conditions in both tables:

     

    consume per econres categoria R = CALCULATE(
    	DIVIDE(
    		SUM(TableName[consume]),
    		SUM(TableName[econres])
    	),
    	FILTER(
    		TableName,
    		TableName[categoria] = "R" &&
    		TableName[OtherColumn] = "X" 
    	),
    	FILTER(
    		OtherTable,
    		OtherTable[ColumnName] = "Y"
    	)
    )

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Two ways to do this. First, if you only need this for categoria "R" then you can explicitly write the measure to only use that:

     

    consume per econres categoria R = CALCULATE(
    	DIVIDE(
    		SUM(TableName[consume]),
    		SUM(TableName[econres])
    	),
    	FILTER(
    		TableName,
    		TableName[categoria] = "R"
    	)
    )

    The second method would apply if you also want to do the same calculation for categorias I, P, C, and M. In that case you can simply write the basic form of the above measure:

     

    consume per econres = DIVIDE(
    	SUM(TableName[consume]),
    	SUM(TableName[econres])
    )

    ...and plot that measure against the categoria column (that is, use categoria as the rows in a matrix, or the legend or X axis in some chart). The context from that column will make the measure give you each categoria, behaving just like the filter statement in my first example.

    • bolabuga's avatar
      bolabuga
      Helper V

      Thks KHorseman, took me sometime to unsderstand i have the value i want repeated all across the 1.7kk rows and i can just throw it on my graphic and choose to show "average" instead of "sum".

       

      obs: i cant say for sure the option correct translation is average, for me its showing in portuguese "media"

    • bolabuga's avatar
      bolabuga
      Helper V

      would it be possible to add more than 1 filter in the "calculate" command??

       

      consume per econres categoria R = CALCULATE(
      	DIVIDE(
      		SUM(TableName[consume]),
      		SUM(TableName[econres])
      	),
      	FILTER(
      		TableName,
      		TableName[categoria] = "R"
      	)
      )
      • Anonymous's avatar
        Anonymous
        Not applicable

        bolabuga you can have more than one filter and more than one condition per filter.

         

        If you want to filter based on more than one column in the same table:

         

        consume per econres categoria R = CALCULATE(
        	DIVIDE(
        		SUM(TableName[consume]),
        		SUM(TableName[econres])
        	),
        	FILTER(
        		TableName,
        		TableName[categoria] = "R" &&
        		TableName[OtherColumn] = "X" 
        	)
        )

         && is "and", meaning both conditions must be met. Substitute that with || if you want to do an "or" condition. You can string together as many conditions as you like this way.

         

        If you have two tables connected by a relationship, and you want to filter based on conditions in both tables:

         

        consume per econres categoria R = CALCULATE(
        	DIVIDE(
        		SUM(TableName[consume]),
        		SUM(TableName[econres])
        	),
        	FILTER(
        		TableName,
        		TableName[categoria] = "R" &&
        		TableName[OtherColumn] = "X" 
        	),
        	FILTER(
        		OtherTable,
        		OtherTable[ColumnName] = "Y"
        	)
        )
    • Anonymous's avatar
      Anonymous
      Not applicable

      Thanks! This is exactly what I needed!

      My final based on your sample is here:

      Measure2 = CALCULATE(DIVIDE(SUMX(Table1,[measure1]*[Column1]),[measure1]), FILTER(Table1, Table1[Column1] <> 999))