topic Re: Same DAX measure gives different Result (PBIX attached) in DAX Commands and Tips

Same DAX measure gives different Result (PBIX attached)

Rabi — Mon, 20 May 2024 03:19:17 GMT

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

Re: Same DAX measure gives different Result (PBIX attached)

Biya — Mon, 20 May 2024 05:14:49 GMT

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

Re: Same DAX measure gives different Result (PBIX attached)

Mahesh0016 — Mon, 20 May 2024 07:41:52 GMT

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!

Re: Same DAX measure gives different Result (PBIX attached)

Rabi — Mon, 20 May 2024 08:59:52 GMT

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.

Re: Same DAX measure gives different Result (PBIX attached)

Mahesh0016 — Mon, 20 May 2024 09:01:51 GMT

Hello Rabi,

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

Re: Same DAX measure gives different Result (PBIX attached)

Rabi — Mon, 20 May 2024 13:35:32 GMT

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