Measure Does Not work Properly.

[Rabi]

Full Name	Contract Hours	Worked Hours	Pay date
ABC	76	7.6	15/01/2024
ABC	76	7.6	15/01/2024
ABC	76	14	15/01/2024
JOHN	42	6	28/01/2024
JOHN	42	25	28/01/2024
JOHN	42	3	28/01/2024

Hi Everyone, 

Above is the sample data of the timesheet.

I am getting correct results when i have a single pay date selected but when i add the same measure in matrix and add pay date in the column, i am not getting correct results I am not sure why.

1 st measure to calculate Hours Worked

Worked Hours= sum('Table A'[Worked Hours])

2nd measure to calculate contract hours as Contract hours is done for fortnight

Contract Hours = 
    SUMX(
        SUMMARIZE(
            'Table A', 
            'Table A'[Full Name], 
            "CONTRACT", max('Table A'[Contract Hours])
        ), 
        [CONTRACT]
    )

 3 rd to calculate difference

Difference= [Worked Hours]-[Contract Hours]

I am checking the variance of the hours so in the matrix i am doing Difference is less than 0 in the filter. 

Please can someone help me with this

Thanks verymuch !!

[v-yiruan-msft]

@Mahesh0016  Thanks for your contribution on this thread.

Hi @Rabi ,

I created a sample pbix file(see the attachment) base on your provided sample data and measures, it can return the expected result. How did you set your matrix visual? Is something missing here?

Best Regards

[Rabi]

@v-yiruan-msft  can you please have a look at this thread https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Same-DAX-measure-gives-different-Res... i have attached a sample PBIX with the result i want to achieve.

Thank you !!

[v-yiruan-msft · Accepted solution]

Hi @Rabi ,

You can create another measure as below and apply a visual-level filter to filter the data which [Difference] is less than 0. Please find the details in the attachment.

Flag = IF( MINX(ALLSELECTED('SAMPLE'[Pay date]),[Difference]) < 0, 1)

Best Regards

[Rabi]

Hi @v-yiruan-msft , I appreciate your help, But I need to have only negative values in the matrix as I have thousands of employee data to show.

Thanks !!

[Mahesh0016]

Hello @Rabi,

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

[Rabi]

@Mahesh0016   can you please have a look at this thread https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Same-DAX-measure-gives-different-Res... i have attached a sample PBIX with the result I want to achieve

Thank you !!