Forum Discussion
Creating a SUM by filter within a table
I think you really should create a measure for this, not a column. If you go the column route, then what you will want to do is NOT sum it in the column formula, so, in other words:
Under Construction = IF('Property Data'[Building Status]="Under Construction",'Property Data'[Number Of Units],0)Then, you can use the standard SUM aggregation feature to summarize this column in your visuals.
I think you really should create a measure for this, not a column. If you go the column route, then what you will want to do is NOT sum it in the column formula, so, in other words:
Under Construction = IF('Property Data'[Building Status]="Under Construction",'Property Data'[Number Of Units],0)Then, you can use the standard SUM aggregation feature to summarize this column in your visuals.
Awesome! Thank you. switching it to a measure worked and the new formula you gave me works.
I still don't understand all the differences between the measure and column. Thanks for the help.
Couple differences between measures and columns. Columns are executed in the row context of the table they are in and are intended to add additional calculations about that particular row to a table. In addition, in my experience, custom column calculations are not generally RLS aware or even filter aware to some degree and essentially get calculated once, at the time of table load or custom column creation.
Measures are executed within the context of where they are at the moment and are RLS aware. Measures are intended to handle calculations about an aggregation of rows. Essentially, think of them as "just in time" calculations. When a visualization gets rendered, the measure evaluates itself within the context in which it currently exists.
So, for example, I can have a measure that is Measure = SUM([Column]). If I put this measure in a table visualization with a "Category" field, it will be one set of values but if I instead chose to put it into a table with "Customer" it will be different values. In addition, if I would potentially filter out the underlying table and get rid of rows with a Status of "Pending", then the measure would have yet another set of values.
Columns on the other hand, once calculated pretty much are what they are and, again, are intended to communicate information about a particular row, not really an aggregation of rows.
