Matrix Table Total Row not calculating totals

[kingchad5]

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 am using a Matrix table to show the data and when I show the row total it is showing 0.00 as the total rather than the total available hrs.  The formula is below.  

AvailableHrsbyDay = IF([Scheduled Hrs]>7,0,9-[Scheduled Hrs])

[Scheduled Hrs] is a measure that totals just fine.

Any help would be greatly appreciated.

Chad

[kliberty]

Please vote for this problem so it can be resolved:

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/17298271-matrix-totals-only-as-su...

[dedelman_clng]

Row totals on measures do not function the same way in PBI as in Excel (summing all of the above values).  PowerBI calculates the measure in the context of the row total (no/less filters).  So [Schedule Hrs] is > 7 at that level so it calculates [Available Hrs] as 0.

You will need combinations of ISFILTERED on the different row values and SUM or SUMX to get the measure to calculate correctly at the aggregate level.

Something like:

AvailableHrs :=
IF (
    ISFILTERED ( Table[Name] ),
    IF ( [Scheduled Hrs] > 7, 0, 9 - [Scheduled Hrs] ),
    SUMX ( Table, 9 - [Scheduled Hrs] )
)

Hope this helps

David

[kingchad5]

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]

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]

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?

[v-yuezhe-msft]

@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]

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?

   

[v-yuezhe-msft]

---

---

 What Table1[Name] should I be referencing?

@kingchad5,

The Name field of your table.


Regards,
Lydia Zhang

[kingchad5]

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

[v-yuezhe-msft · Accepted solution]

@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]

This worked!  Thanks for all your help.