Median value is getting disturbance after sorting fiscal month number

[mohittimpus]

I have 2 Tables:

1) Calender

2) Employee

'Date' master table is connected with Employee_Info.

I have created one table visualization inside Employe_Info.pbix file. Table is filter with Year.

1) Inside Employee.pbix "Fiscal Month" is taking from Calender table, after that I created one Measure with name "#Count Emp" which calculate total 'Count of 'PU Insertion Date' column.

#Count Emp = SUM(Employee_Info[Count of Employee])

2) finally I am calculating 'Median' of "#Count Emp" for that I have created Measure with name "Median_of_Emp" with couple of steps followed inside "Median_of_Emp" Measure. 

Median_of_Emp =

VAR _tbl =

SUMMARIZE(

CALCULATETABLE(

'calender',

ALLSELECTED( 'calender'[Fiscal Month])

),

'calender'[Fiscal Month]

)

RETURN

IF(

[#Count emp] > 0,

INT(MEDIANX( _tbl, [#Count emp] ))

)

****** I am getting exact Median,  Now I am facing problem with "Median_of_Emp" Measure. When I sort fiscal month with 'FiscalMonthNBR' column of Calender table. it is giving me same value of Measure #Count of emp. I need help for it. It should be give correct median after sorting by 'FiscalMonthNBR' column. 

Below I am sharing 2 screenshot one is before sorting and one is after sorting :

 

Below I am sharing .PBIX file also.

https://drive.google.com/open?id=12vyxAkPq7CnSEKbb05E-0YcEjbfGYsBO

[v-lid-msft]

Hi @mohittimpus ,

Sorry for that, we can not access your shared pbix file in Google Drive, but the two tables seems exactly same as each other from your

shared screenshot. We tried to reproduce with mocked sample data, but your measure have expecetd result on my side.

If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

Best regards,

[mohittimpus]

@v-lid-msft 

I am sharing a link:

https://systematixinfotech235-my.sharepoint.com/:u:/g/personal/mayank_khandelwal_systematixindia_com...

When I sort fiscal month with 'FiscalMonthNBR' column of Calender table. It is showing same value as Count of Emp. Median should be 36 after sorting by 'FiscalMonthNBR' column of Calender table.

I need help for this.

[v-lid-msft]

Hi @mohittimpus ,

We can use the following measure as a workaround:

Median_of_Emp =
VAR _tbl =
    ADDCOLUMNS (
        GROUPBY ( ALLSELECTED ( 'Table' ), 'Table'[Fiscal Month] ),
        "Count",
        VAR fm = [Fiscal Month]
        RETURN
            CALCULATE (
                SUM ( 'Employee_Info'[Count of Employee] ),
                CALCULATETABLE (
                    ALLSELECTED ( 'Employee_Info' ),
                    FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Fiscal Month] = fm )
                )
            )
    )
RETURN
    INT ( MEDIANX ( _tbl, [Count] ) )

Best regards,

[mohittimpus]

It is not working in my given table in .pbix file. I need in my Calender table only. you have created another table. I don't need another table. Fiscal Month should sort by 'FiscalMonthNBR' column from Calender table.

Below I am sharing .pbix file link:

https://systematixinfotech235-my.sharepoint.com/:u:/g/personal/mayank_khandelwal_systematixindia_com...

[v-lid-msft]

Hi @mohittimpus ,

Sorry for that we forget the change the column name, the another table is the calendar table, have you tried to change the column name in formula?

Median_of_Emp =
VAR _tbl =
    ADDCOLUMNS (
        GROUPBY ( ALLSELECTED ( 'calender' ), 'calender'[Fiscal Month] ),
        "Count",
        VAR fm = [Fiscal Month]
        RETURN
            CALCULATE (
                SUM ( 'Employee_Info'[Count of Employee] ),
                CALCULATETABLE (
                    ALLSELECTED ( 'Employee_Info' ),
                    FILTER ( ALLSELECTED ( 'calender' ), 'calender'[Fiscal Month] = fm )
                )
            )
    )
RETURN
    INT ( MEDIANX ( _tbl, [Count] ) )

Sorry again for that we did not test with your shared file since it cannot be downloaded.

Best regards,

[mohittimpus]

Please use this below google drive link:

https://drive.google.com/open?id=18hrMx-i9L8G5rWsx96GCenwFHLC0jbzs

Please get Employee.pbix file.

[v-lid-msft]

Hi @mohittimpus ,

It seems an issue with "Sory By Column" feature, we will try to report and try to provide a workaround for your scenario.

Best regards,

[v-lid-msft]

Hi @mohittimpus ,

Thank you for your understanding, but unfortunately, we can not test with the DirectQuery mode(Please do not share any other information about real data). We will try to use other ways to solve this problem. Based on your shared screenshot, does the Fiscal Month NBR column increase by the Fiscal Month Column? Does our formula get some error information associated with the DirectQuery mode?

Best regards,

[mohittimpus]

Please find below Employee_Information.pbix file link:

It is not DirectQuery mode.

https://drive.google.com/open?id=1sbpX3bLP_vVeVKb6X9V_MjBWCNyEkmNz

Please get Employee_Information.pbix file.

[v-lid-msft]

Hi @mohittimpus ,

Have your issue be resolved?Could you please provide more details about it If it still not be soleved? Please don't have any Confidential Information or Real data in your reply.

Best regards,

[v-lid-msft]

Hi @mohittimpus ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[v-lid-msft]

Hi @mohittimpus ,

We found that this hebaviour  is  by design. When a column [A] is sorted by another column [B], adding [A] to a report implicitly also add [B] to the report even though [B] is invisible. As a result, the measure must apply ALL to both [A] and [B].

Please refer to the following threads: https://community.powerbi.com/t5/Issues/Bug-with-Power-BI-Desktop-ALL-function/idi-p/181454

https://community.powerbi.com/t5/Desktop/DAX-quot-ALL-quot-function-not-working-on-sorted-columns/td...

Best regards,

[v-lid-msft]

Hi @mohittimpus ,

We can try to use the following measure to meet your requirement:

Median_of_Emp = 
VAR _tbl =
    SUMMARIZE ( ALLSELECTED ( 'Calender' ), 'Calender'[Fiscal Month] )
RETURN
    IF (
        [Employee Count] > 0,
        INT (
            MEDIANX (
                _tbl,
                CALCULATE (
                    [Employee Count],
                    FILTER (
                        ALLSELECTED ( 'Calender' ),
                        [Fiscal Month] = EARLIER ( Calender[Fiscal Month] )
                    )
                )
            )
        )
    )

Best regards,