Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

MATRIX, Missing Data on Column Subtotals

Hi there,   I have this matrix that is built from a DAX that sums the total hours (Table A) spend for each team and multiplied it by the cost by hour of that respective team (Table B, see snip) and...
Need Help
  • jgeddes's avatar
    jgeddes
    3 years ago

    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. 

     

jgeddes's avatar
jgeddes
Super User
3 years ago

It is hard to be 100% certain but I believe that you are correct in assuming the issue is the teams with different rates for departments and complexities.
To correct this you have to ensure that each hours row has the correct rate applied to it, then the sums will give you the desired result.

To illustrate I created a small dataset of hours and rates.

I then added calculated columns in the hours table that looks up the rate for each row and then multiplies that by the hours

Applied Rate =
LOOKUPVALUE(rateTable[Rate],rateTable[Department],hoursTable[Department],rateTable[Rate Level],hoursTable[Level],rateTable[Team],hoursTable[Team])
Rate Cost =
[Applied Rate] * [Hours]
 
With each row of hours having the correct rate applied you can now write a sumx measure to use in a visual
Rate Cost Total =
SUMX(hoursTable,hoursTable[Rate Cost])
 

This is a bit of an oversimplified example but hopefully it gets you pointed in the right direction.

 
 
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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:

    Getting the rate =
    LOOKUPVALUE(
        'VENDOR-RATES'[RATE]
            ,'VENDOR-RATES'[CONCAT_COLUMN]
            ,SELECTEDVALUE('All Activity  All Nodes'[CONCAT_COLUMN])
    )

     

    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???

    • jgeddes's avatar
      jgeddes
      Super User
      3 years ago

      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. 

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Thanks for the suggestion, I'll check that and see if that gives me the expected result.