Forum Discussion
Calculation between columns on a table
- Anonymous9 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.
- Anonymous9 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" ) )
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" ) )
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" ) )
- bolabuga9 years agoHelper V
Nice Explanation, really thks KHorseman.