Total Sum by Column (Matrix)

[Belle2022]

Created a gantt chart using matrix. I want to display the row total count with value for each date. Below is the image on the table i've been working out, together with the formula that I used to display the value in the table. 

This is my current Gantt Chart.This is the output that I intended to do.

This is the formula that I used to get the value in the matrix :

column values =

VAR _startdate =

CALCULATE(

min('Task Tracker'[start date]),

all('Date'[Date])

)

var _enddate =

calculate(

max('Task Tracker'[expected end date]),all('Date'[Date])

)

var _status =

CALCULATE(max('Task Tracker'[current status]),all('Date'[Date]))

var _period =

min('Date'[Date]) >= _startdate && max('Date'[Date])<=_enddate

return

switch(

true(),

_period && _status = "Completed",1,

_period && _status = "In progress",2,

_period && _status = "Overdue",3,

_period && _status = "On Hold",4,

_period && _status = "Yet to start",5)

[v-angzheng-msft · Accepted solution]

Hi, @Belle2022 

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.

[lbendlin]

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?

[Belle2022]

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!

[Belle2022]

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)

[lbendlin]

that can be rewritten as

_period && _status IN { "Completed","In progress", "Overdue","On Hold","Yet to start"},1

[Belle2022]

okay 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

[Anonymous]

if source is your tab,

then open an empty query and paste the following code ovewriting all.

let

Start_End = (SD, ED, optional Culture as nullable text) as table => 
  let
    StartDate= Date.FromText(SD,[Format="dddd d MMMM yyyy", Culture=Culture]),
    EndDate= Date.FromText(ED,[Format="dddd d MMMM yyyy", Culture=Culture]),
    DayCount = Duration.Days(Duration.From(EndDate - StartDate))+1, 
    Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)), 
    TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),    
    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}), 
    RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
    res= Table.AddColumn(RenamedColumns, "gant", each 1)
  in 
    res,

     #"Grouped Rows" = Table.Group(source, {"Category", "Name", "Task Type", "Assigned To"}, {"from to", each Start_End([Start Date]{0},[Expected End Date]{0},"it-IT")}),
    #"Expanded from to" = Table.ExpandTableColumn(#"Grouped Rows", "from to", {"Date", "gant"}, {"Date", "gant"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded from to", {{"Date", type text}}, "it-IT"), List.Distinct(Table.TransformColumnTypes(#"Expanded from to", {{"Date", type text}}, "it-IT")[Date]), "Date", "gant", List.Sum),
    #"Grouped Rows1" = Table.Group(#"Expanded from to", {"Date"}, {{"total", each List.Sum([gant]), type number}}),
    #"Transposed Table" = Table.Transpose(#"Grouped Rows1"),
     GranTotal = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])
      in 
      Table.Combine({#"Pivoted Column",GranTotal})

[Anonymous]

All done in Power Query, although probably the part of the grant calculation can be done more easily in the BI desktop environment.

[lbendlin]

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with. Avoid posting screenshots of your source data if possible.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Belle2022]

Sure, attached link here is the sample pbi & the expected output. Lemme know if you have the solution. Thanks!

PBI & Expected output