Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mohittimpus
Helper V
Helper V

How to get Measure value Replicate in all Row with respect to Fiscal Month

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. 

Empoyee_Info.png

 

I have shared .pbix file with name Employee_Info.pbix in below link

https://www.dropbox.com/s/w1yn3hjt2c7es1c/Employee_Info.pbix?dl=0

3 ACCEPTED SOLUTIONS
Mariusz
Community Champion
Community Champion

HI @mohittimpus 

 

Try the below

#Count Employee = COUNTROWS( Employee_Info )
Median_of_Emp = 
MEDIANX( ALL( 'Date'[Fiscal Month] ), [#Count Employee] )
Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

 

View solution in original post

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] ) )

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

View solution in original post

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.
image.png

 

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] ) )
)

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

 

View solution in original post

5 REPLIES 5
Mariusz
Community Champion
Community Champion

HI @mohittimpus 

 

Try the below

#Count Employee = COUNTROWS( Employee_Info )
Median_of_Emp = 
MEDIANX( ALL( 'Date'[Fiscal Month] ), [#Count Employee] )
Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

 

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. 
Screenshot_1.png
But I want 38 in all rows which is showing in below Image.

 

Empoyee_Info.png

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] ) )

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

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. 

 

Screenshot_4.png

 

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.
image.png

 

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] ) )
)

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.