topic How to subtotal in matrix with lookup values in measure in DAX Commands and Tips

How to subtotal in matrix with lookup values in measure

simontam — Mon, 20 Feb 2023 05:52:33 GMT

I need some help in summing/subtotalling a column of values that are made up of a measure which does a lookupvalue.

I have a matrix report. In it are grouped projects and tasks within each project. I created a measure that does a lookupvalue to get the budgeted hours for each of those tasks based on the project name/task name. That seems to work fine. However subtotalling at the project level does not and also there is no grand total. The lookupvalue doesn't make sense to do at a project level without a task name.

The column Budgeted Hours is this measure:

budgethourlookupt = if(ISINSCOPE(cr7e9_timesheet[Program Task]), LOOKUPVALUE(cr7e9_programtasks[Budget Hours],cr7e9_programtasks[cr7e9_program],selectedvalue(cr7e9_timesheet[cr7e9_program]),cr7e9_programtasks[Name],SELECTEDVALUE(cr7e9_timesheet[Name 2]),0))

I'm not sure what to put in when it's not in scope (at the project level) that would represent what the subtotal of the project's tasks budgeted hours are.

I imagine if the subtotals can be figured out then the grand total will naturally appear (or I'll have to use the HASONEVALUE to determine when to do a grand total).

I hope someone can hep me figure this out.

Re: How to subtotal in matrix with lookup values in measure

amitchandak — Mon, 20 Feb 2023 05:54:44 GMT

simontam , Try like

budgethourlookupt = Sumx( values(cr7e9_timesheet[Program Task]) ,calculate( if(ISINSCOPE(cr7e9_timesheet[Program Task]), LOOKUPVALUE(cr7e9_programtasks[Budget Hours],cr7e9_programtasks[cr7e9_program],selectedvalue(cr7e9_timesheet[cr7e9_program]),cr7e9_programtasks[Name],SELECTEDVALUE(cr7e9_timesheet[Name 2]),0)) ) )

Re: How to subtotal in matrix with lookup values in measure

simontam — Mon, 20 Feb 2023 06:37:20 GMT

Ok you were very close, I had to modify your formula a bit (removed the IF(INSCOPE...), perhaps you meant that. Doing that the results are now subtotalling like I wanted.

Thanks for the super fast response.

budgethourlookupt = SUMX(VALUES(cr7e9_timesheet[cr7e9_programtask]), CALCULATE(LOOKUPVALUE(cr7e9_programtasks[Budget Hours],cr7e9_programtasks[cr7e9_program],SELECTEDVALUE(cr7e9_timesheet[cr7e9_program]),cr7e9_programtasks[Name],SELECTEDVALUE(cr7e9_timesheet[Name 2]),0)))