Forum Discussion

kingchad5's avatar
kingchad5
Icon for Helper I rankHelper 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
Icon for Helper I rankHelper 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?