Incorrect Totals

[_bs_]

Hello, working on first Power BI report and having issues.

The objective is to determine for each Engineer/Resource how many hours and days they are available to work in a calendar month individually and as a overall Team

Table Working Dates is filtered to show working days in the current month

Table Resources is filtered to one Team consisting of 5 Resources.

There is no relationship between them as I expect values to be duplicted per Resource / per row.

This matix is showing each Resource has 21 days / 168 hours in a month -  this is correct.

The issue is the total, I would like to see 105 days / 840 hours.

The Formulas I have tried are:

Working Days = CALCULATE(DISTINCTCOUNT('Working Dates'[Date]),ALL(Resources[Engineer]))

Working Days = COUNTX('Working Dates','Working Dates'[Date])

Any suggestions? Thank you

[danextian · Accepted solution]

Hi @_bs_ 

Your working days and working hours calculations need to be evaluated for each distinct engineer value and then summed up. To achieve this, try these measures:

SUMX ( VALUES ( data[engineer] ), [working days formula] )

or

SUMX (
    ADDCOLUMNS (
        SUMMARIZE ( data, data[engineer] ),
        "@value", [working days formula]
    ),
    [@value]
)

[_bs_]

Thank you @danextian 

I have used the first option and works correctly 😀