Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 is split by complexity level and by Department. A single Team can have a different rate either by complexity or by Department.

 

MSA_BB123_2-1663875162799.png

 


The matrix also has the measure that gives me the right total for each column. Everything fine so far but, as you can see on the snip, some of the total in the Column Subtotals did not appear (highlighted in red), and the cell in green also did not show up although there are hours and rates assigned for that category and the calculation is done it (see second snip). 

MSA_BB123_0-1663874602820.png

MSA_BB123_1-1663874888301.png

 

The thing is the cells that are not showing up are the ones that have different rates for each department or Complexity, and I assume that the system chooses not to guess which one shows but:


1- there is a way to force the figure to show up??? 


2- there is a way to choose/ manipulate with Dax or similar the totals that show on the right of the matrix (green rectangle) in the same way that we can choose what to see on the lower ones (red rectangle) that use a measure like 

RATE COST RIGHT TOTAL =
SUMX( VALUES('All Activity  All Nodes'[TEAM]), [RATE COST II] )

 

MSA_BB123_3-1663875652324.png

 

Thanks in advance!

 

1 ACCEPTED SOLUTION

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. 

 

View solution in original post

6 REPLIES 6
v-henryk-mstf
Community Support
Community Support

Hi @Anonymous ,

 

Whether the advice given by @jgeddes  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.


Looking forward to your feedback.


Best Regards,
Henry

jgeddes
Super User
Super User

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.

jgeddes_0-1663880073186.png

jgeddes_1-1663880086923.png

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

jgeddes_2-1663880168407.png

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])
 
jgeddes_3-1663880321796.png

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

 
 
Anonymous
Not applicable

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

MSA_BB123_0-1663884511621.png

 

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

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
Not applicable

It took me a while but once all the rows with missing RATE assignation where clean the SUM funtion shows the totals.

 

Thanks! 

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.