Forum Discussion

kingchad5's avatar
kingchad5
Helper I
9 years ago
Solved

Matrix Table Total Row not calculating totals

I have a meaure to cacluate available hours for someones schedule.  if the person is scheduled more than 7 hrs i want to show it as 0 and if they are under 8 hrs I want to show what is available.  I ...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    kingchad5,

    Please change your DAX to the following:

    AvailableHrsbyDay4 = IF(COUNTROWS(VALUES(wh_resource[Name]))=1,
    IF([Scheduled Hrs]>=0 && [Scheduled Hrs] < 9,9-[Scheduled Hrs],0),
    SUMX(VALUES(wh_resource[Name]), IF([Scheduled Hrs]>=0 && [Scheduled Hrs] <9,9-[Scheduled Hrs],0)
    ))

    Regards,

Anonymous's avatar
Anonymous
Not applicable
9 years ago

kingchad5,

Use the following DAX to create the measure, then check if you get expected result.

AvailableHrsbyDay = IF(COUNTROWS(VALUES(Table1[Name]))=1,
   IF([Scheduled Hrs]>7,0,9-[Scheduled Hrs]),
   SUMX(VALUES(Table1[Name]), IF([Scheduled Hrs]>7,0,9-[Scheduled Hrs]))
)




Regards,
Lydia Zhang

kingchad5's avatar
kingchad5
Helper I
9 years ago

I needed to update the Table1[Name] reference to a table I have, but I think I need to reference the Scheduled Hrs table, but that is a meausre not a Column.  when I added just a table name to the VALUE function i get close, but I beleive when the IF is FALSE the data returned is incorrect.  What Table1[Name] should I be referencing?

 

   

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

     

       

     What Table1[Name] should I be referencing?


    kingchad5,

    The Name field of your table.


    Regards,
    Lydia Zhang

    • kingchad5's avatar
      kingchad5
      Helper I
      9 years ago

      Thanks for all  your help with this!  
       
      That got the numbers correct, but the total is still off.  The total is 16 where it should be 11
       
      Current forumla;
       
      AvailableHrsbyDay4 = IF(COUNTROWS(VALUES(wh_resource[Name]))=1,
      IF([Scheduled Hrs]>=0 && [Scheduled Hrs] < 9,9-[Scheduled Hrs],0),
      SUMX(VALUES(wh_service_call), IF([Scheduled Hrs]>=0 && [Scheduled Hrs] <9,9-[Scheduled Hrs],0)
      ))

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        kingchad5,

        Please change your DAX to the following:

        AvailableHrsbyDay4 = IF(COUNTROWS(VALUES(wh_resource[Name]))=1,
        IF([Scheduled Hrs]>=0 && [Scheduled Hrs] < 9,9-[Scheduled Hrs],0),
        SUMX(VALUES(wh_resource[Name]), IF([Scheduled Hrs]>=0 && [Scheduled Hrs] <9,9-[Scheduled Hrs],0)
        ))

        Regards,