Forum Discussion
Total Sum by Column (Matrix)
Hi, Anonymous
Try to create a measure like this:
_Measure = SUMX('sample data',[column values])Result:
Please refer to the attachment below for details.
Hope this helps.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for providing the sample data.
Please remember that in a matrix visual each measure is calculated four times. Once for the individual cells, but then also for the Column totals, the row totals , and finally for the Grand Total.
Accordingly your measure will need to take all these scenarios into account. Your current formula only works for the cells.
Note: your "all('Date'[Date])" filter is not providing any help as your tables are disconnected (which they need to be for your scenario).
There are also some typos in your code. Here's a cleaned-up version
column values =
VAR _startdate = min('sample data'[Start Date])
var _enddate = max('sample data'[Expected End Date])
var _status = max('sample data'[Current Status])
var _period = min('Date'[Date]) >= _startdate && max('Date'[Date])<=_enddate
return switch(true(),
_period && _status = "Complete",1,
_period && _status = "In progress",2,
_period && _status = "Overdue",3,
_period && _status = "On Hold",4,
_period && _status = "Yet to start",5)Now the question is - what result do you expect for the Column subtotals? Do they make sense in your scenario?
Im expecting the totals for each columns in which the cells have value - doesnt matter the value 1,2,3,4, or 5, it will count as 1. Lemme know if you have the solution thnks!
Or we can change the values as 1 and get the total for each columns
_period && _status = "Completed",1,_period && _status = "In progress",1,_period && _status = "Overdue",1,_period && _status = "On Hold",1,_period && _status = "Yet to start",1)that can be rewritten as
_period && _status IN { "Completed","In progress", "Overdue","On Hold","Yet to start"},1okay but is there any ways that we can get the totals at the bottom for each date consisting 1? as my current formula only display 1 as total in the matrix