Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have 2 Tables:
1) Date
2) Employee_Info
'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_Info.pbix "Fiscal Month" is taking from Date table, after that I created one Measure with name "#Count Employee" which calculate total 'Count of Employee' column.
#Count Employee = CALCULATE(SUM(Employee_Info[Count of Employee]))
2) After that I created Measure with name "Rank of Employee" Which is getting rank of Employee based on fiscal Month.
Rank of Employee = if([#Count Employee]>0 || [#Count Employee]<>BLANK(),RANKX (FILTER(ALLSELECTED('Date'[Fiscal Month]),[#Count Employee]<>0 || [#Count Employee]<>BLANK()), [#Count Employee],,ASC,Dense))
3) finally I am calculating 'Median' of "#Count Employee" 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 Step1for_Emp_Median = [#Count Employee]
VAR Step2for_Emp_Median = IF([#Count Employee]>0,CALCULATE(CALCULATE(DISTINCTCOUNTNOBLANK('Date'[Fiscal MonthNumber]), FILTER(Employee_Info,[#Count Employee]>0)),ALL('Date'[Fiscal Month])))
VAR Step3for_Emp_Median = IF([#Count Employee]>0,CALCULATE( IF(MOD(Step2for_Emp_Median,2)=0, Step2for_Emp_Median/2, (Step2for_Emp_Median+1)/2) ,all('Date'[Fiscal Month])))
VAR Median_of_Emp = IF([Rank of Employee]=Step3for_Emp_Median,[#Count Employee])
Return Median_of_Emp
******Now I am facing problem with "Median_of_Emp" Measure. Whatever value I am getting form "Median_of_Emp" Measure, that value I want to Replicate with all Rows with respect to "Fiscal Month".
In below image I have Mention my required output with red text for entire table output.
I have shared .pbix file with name Employee_Info.pbix in below link
https://www.dropbox.com/s/w1yn3hjt2c7es1c/Employee_Info.pbix?dl=0
Solved! Go to Solution.
HI @mohittimpus
Try the below
#Count Employee = COUNTROWS( Employee_Info )
Median_of_Emp =
MEDIANX( ALL( 'Date'[Fiscal Month] ), [#Count Employee] )
Hi @mohittimpus
It actually is 38.5, however, if for whatever reason you don't trust Microsofts Median Function and you wish to round it down, then you can use the below.
Median_of_Emp =
INT( MEDIANX( ALL( 'Date'[Fiscal Month] ), [#Count Employee] ) )
Hi @mohittimpus
You need to change your Year Slicer Field to one of the fields from the 'Date' table, you are currently using Hierarchy 'Date'[Date].[Year] in the visual and this can cause a lot of complications.
I would suggest 'Date'[Fiscal Year] as you are using 'Date'[Fiscal Month] in your Table Visual
Please see the below screenshot.
and adjusted DAX expression.
Median_of_Emp =
VAR _tbl =
SUMMARIZE(
CALCULATETABLE(
Employee_Info,
ALLSELECTED( 'Date'[Fiscal Month] )
),
'Date'[Fiscal Month]
)
RETURN
IF(
[#Count Employee] > 0,
INT( MEDIANX( _tbl, [#Count Employee] ) )
)
HI @mohittimpus
Try the below
#Count Employee = COUNTROWS( Employee_Info )
Median_of_Emp =
MEDIANX( ALL( 'Date'[Fiscal Month] ), [#Count Employee] )
Hello @Mariusz
Thank you for Reply.....
According to your solution I am getting 39 but it should be 38. Below image I am sharing you what I got.
But I want 38 in all rows which is showing in below Image.
Hi @mohittimpus
It actually is 38.5, however, if for whatever reason you don't trust Microsofts Median Function and you wish to round it down, then you can use the below.
Median_of_Emp =
INT( MEDIANX( ALL( 'Date'[Fiscal Month] ), [#Count Employee] ) )
Hello @Mariusz
If I have only 3 month in any Year filter then It is giving me Blank value. Below image is showing Blank Value but It should give me 40 Value according to 3 month value Median.
Hi @mohittimpus
You need to change your Year Slicer Field to one of the fields from the 'Date' table, you are currently using Hierarchy 'Date'[Date].[Year] in the visual and this can cause a lot of complications.
I would suggest 'Date'[Fiscal Year] as you are using 'Date'[Fiscal Month] in your Table Visual
Please see the below screenshot.
and adjusted DAX expression.
Median_of_Emp =
VAR _tbl =
SUMMARIZE(
CALCULATETABLE(
Employee_Info,
ALLSELECTED( 'Date'[Fiscal Month] )
),
'Date'[Fiscal Month]
)
RETURN
IF(
[#Count Employee] > 0,
INT( MEDIANX( _tbl, [#Count Employee] ) )
)
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |