measure total does not equal sum of row values

[Anonymous]

I have the following 4 measures:

Hours Overforecasted = 
VAR Over = [Fcst Hours] - [Number of Weeks]*[Max Weekly Hours - selected value] //compute the difference between Fcst Hours and Max Weekly Hours
VAR EmpStatus = MIN('Dimension Employee Weekly Schedule'[Emp Status]) //get the employee's status

//if the employee is terminated, then return [Fcst Hours]
//if Over is positive, then return it; else, return blank
RETURN
    IF(
        EmpStatus = "Terminated"
        ,[Fcst Hours]
        ,IF(
            Over > 0
            ,Over
            ,BLANK()
        )
    )

Fcst Hours = 
SUM('Fact Fcst'[Hours])

Number of Weeks = 
DISTINCTCOUNT('Dimension Employee Weekly Schedule'[Week Key])

Max Weekly Hours - selected value = SELECTEDVALUE('Max Weekly Hours Slicer'[Max Weekly Hrs])

The user can control 'Max Weekly Hours Slicer'[Max Weekly Hrs] via a slicer.  There are a few other slicers that control the page as well.

The [Hours Overforecasted] measure appears on a table visual that has employee ID as the dimension.  The Totals feature is on.  For every combination of slicer selections, the Totals value does not equal the sum of the row values.  How can I resolve this?

[v-kelly-msft]

Hi @Anonymous ，

Try the measure below：

Total= sumx(summarize('Dimension Employee Weekly Schedule',[Fcst Hours],[Number of Weeks],[Emp Status]),[Hours Overforecasted])

If it doesnt work,could you pls upload your .pbix file to onedrive business and share the link with us?Do remember to remove the confidential information.

Much appreciated.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[TomMartens]

Hey @Anonymous ,

I have to admit that I have my difficulties reading the DAX without being able to look at the visuals and the data model. You might consider creating a pbix that contains sample data but still reflects your data model. Upload the file to onedrive or dropbox and share the link.

Nevertheless, you have to be aware that the total line does not have an implicit filter, as there are row headers e.g. employee ids on rows that implicitly filters underlying tables, there is no employee id in the total line.

Looking at the measure Hours Overforecasted, I'm wondering what the expected result is for EmpStatus.

You might consider to change the measure and use a table iterator function like SUMX

SUMX(
    VALUES('<table that contains the employee id>'[employee id])
    , var Over = ...
    , var EmpStatus = ...
    return
    IF(
        EmpStatus = "Terminated"
    ....)
)

If the variables not dependent on the employee ID you can define the variable outside of the iteration, this avoids unnecessary evaluation.
It can also be necessary to wrap the MIN('Dimension Employee Weekly...[...]) into a CALCULATE to perform context transition if used inside the iterator.

Hopefully, this provides some new ideas to tackle your challenges.

Regards,

Tom

[Greg_Deckler]

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907