Forum Discussion
MATRIX, Missing Data on Column Subtotals
Without seeing the model I am just guessing, but a few things I would be looking at;
Which table are the hours in? Based on what you have provided I am guessing 'All Activity AllNodes' table.
If that is correct, have you created the rate lookup column in the 'All Activity AllNodes' table? And if so, does each row have a value that is not blank?
If each hour line has a coresponding rate value then a sum function should work as long as the fields being added to the visual have appropriate relationships to the 'All Activity AllNodes' table.If there are blanks in the rate column you will need to troubleshoot why i.e. which combinations of Team, Department and Level are not pulling a rate. Verify the rate exists (which it sounds like you have done already). When looking LOOKUPVALUE in the past I have had small spelling descrepancies mess the whole thing up too.
Hi jgeddes , thanks for reply.
I tried before with a LOOKUPVALUE formula similar that the one you mentioned like this:
Getting the rate II =
LOOKUPVALUE( 'VENDOR-RATES'[RATE],
'VENDOR-RATES'[Team],
SELECTEDVALUE('All Activity All Nodes'[TEAM]),
'VENDOR-RATES'[RATE LEVEL],
SELECTEDVALUE('All Activity All Nodes'[LMH Complexity Group]),
'VENDOR-RATES'[Department],
SELECTEDVALUE('All Activity All Nodes'[DEPARTMENT])
)
and after you mentioned it with a simpler version, since I modified the conection between the tables:
but ironically in my case it shows me even less totals on the COLUMN SUBTOTALS
I made sure since the beginning that all the teams have a rate assigned for Design and Drafting otherwise, they would not show up on the result matrix, so that variable has already been considered.
As you mentioned other variables like how the data is set and some others could be modifying my output. Any ideas were to explore???
Super UserWithout seeing the model I am just guessing, but a few things I would be looking at;
Which table are the hours in? Based on what you have provided I am guessing 'All Activity AllNodes' table.
If that is correct, have you created the rate lookup column in the 'All Activity AllNodes' table? And if so, does each row have a value that is not blank?
If each hour line has a coresponding rate value then a sum function should work as long as the fields being added to the visual have appropriate relationships to the 'All Activity AllNodes' table.
If there are blanks in the rate column you will need to troubleshoot why i.e. which combinations of Team, Department and Level are not pulling a rate. Verify the rate exists (which it sounds like you have done already). When looking LOOKUPVALUE in the past I have had small spelling descrepancies mess the whole thing up too.
Thanks for the suggestion, I'll check that and see if that gives me the expected result.
It took me a while but once all the rows with missing RATE assignation where clean the SUM funtion shows the totals.
Thanks!