Same DAX measure gives different Result (PBIX attached)

[Rabi]

Hi Everyone, 

 I am working on employees' contracted vs actual hours and I am checking employees who have worked less than their contracted hours ( ie I wanted to see the difference less than 0). I have a Dax measure which gives me different results when I select a single date and when I have multiple dates in the matrix.

I have created a sample PBIX and attached it.

Below is the Dax i am using:

Actual = sum('SAMPLE'[Worked Hours])

Contract = 
    SUMX(
        SUMMARIZE(
            'SAMPLE', 
            'SAMPLE'[Full name], 
            "Employee Hours", max('SAMPLE'[Fortnightly Contract])
        ), 
        [Employee Hours]
    )

Difference = [Actual]-[Contract]

As you can see in the screenshot the result is different in fact the matrix with all the dates is even missing the complete pay date.

Thanks very much for help, I have tried everything i can.

PBIX below

https://drive.google.com/file/d/1oyGQwY6CkdNiwUGUR-ZuavwvocKUPDJ4/view?usp=sharing 

[Mahesh0016]

Hello @Rabi,

Your answer is correct. When you apply the condition of difference less than 0, it summarizes the actual hours and contract hours first, and then subtracts them. For instance, if there are 72 contract hours for 28/1/2014 and 36 actual hours, and 63 contract hours or 45 actual hours, it will sum up to 72 + 63 = 135 and 36 + 45 = 81. Then, subtracting 135 - 81 gives a difference of 54 hours for the location and employee. Applying the condition < 0 hours will indeed yield a different result compared to individual dates.

@Rabi , I hope you got my point.
thank you!

[Rabi]

Hi @Mahesh0016 

Thanks Very much for the explanation,

is there a work around so that i get the correct hours when i have multiple dates in the matrix. 

[Mahesh0016]

Hello @Rabi,

Could you please share the expected output? This will help us better understand the problem.

[Rabi]

Hi @Mahesh0016 
I am trying to get results as in the single date selected in the screenshot but having them in a same Matrix with date in the columns

Thanks

Rabi

[Biya]

Hi ,

I had same issue for sometime back when i was calculating a statistical value .Actually measure depend on the selected data set and changes value if we select single row or multiple rows.

I resolved the issue by creating a calculated table to calculate country wise statistical value , as follow:

SummaryTable =

VAR CountryData = 'Country data'

VAR SummaryData =

    SUMMARIZE(

        CountryData,

        'Country data '[Country],

        "Q1", PERCENTILE.INC('Country data'[Log_Supply_Demand_Ratio], 0.25),

        "Q3", PERCENTILE.INC('Country data '[Log_Supply_Demand_Ratio], 0.75),

         "IQR", [Q3] - [Q1],

        "Lower Limit", [Q1] - (1.5 * [IQR]),

        "Upper Limit", [Q3] + (1.5 * [IQR]) )

RETURN

    SummaryData

so you can use this apprach for correct calculation ,create table to calculate values employee wise