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,

kingchad5's avatar
kingchad5
Helper I
9 years ago

David,

Thanks for your help.  I can only get the ISFILTERED function to return True when i select one of the slicer values.  Am i trying to get a true value?  When I get a false from the ISFILTERED the total is correct, but the values are not correct.  

 

 

Current formula:

 

AvailableHrsByDay2 =
IF (
ISFILTERED (wh_service_call[BusHrsDuration]),
IF ( [Scheduled Hrs] >=0 && [Scheduled Hrs] <9, 9-[Scheduled Hrs],[Scheduled Hrs]-[Scheduled Hrs] ),
SUMX(wh_service_call,9-[Scheduled Hrs])
)

 

 

 

dedelman_clng's avatar
dedelman_clng
Community Champion
9 years ago

You want to check ISFILTERED on a column used in the rows of the visual.  Your formula is checking for a filter on a duration.

  • kingchad5's avatar
    kingchad5
    Helper I
    9 years ago

    All my columns in my visual are Measures not table columns.  I don't think I was able to ISFILTERED on a measure.  Is there another way to filter the visual on a measure?

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