Creating a SUM by filter within a table

[Brendan_1]

Hi,

My data is a list of buildings (each building in a row). Each building has total number of units, market, and building status (existing or under construction). In my table I have each Market and the total number of units but I'd like to have a serpate column for existing and for under construction. I tried creating a new Column and using SUMX with a FILTER, Under Construction = SUMX(FILTER('Property Data','Property Data'[Building Status]="Under Construction"),'Property Data'[Number Of Units]) 

Background on Data - I have 547 Buildings totaling 99,401 units. Of those 532 bldgs and 97,200 are existing and 15 bldgs and 2,201 units are under construction. I have 7 different markets that all of those buildings fall in. So i should get the 2,201 units spread out by which submarket they are in. 

If the column is set to SUM in the Values then it multiples the amount by how many building records (547*2,201) and if it is Set to Don't Summarize in the Values then the number it returns is the total 2,201 for each submarket. 

Any idea how to do this within the same table. I've been able to create two different tables and have the Visual Level Filter selected on each but I'd much prefer to have them on the same table.

Thanks,

[Greg_Deckler · Accepted solution]

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.

[Brendan_1]

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. 

[Greg_Deckler]

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.