DAX Cookbook by Greg Deckler

Author:Greg Deckler [Greg Deckler]
Language: eng
Format: epub
Tags: COM018000 - COMPUTERS / Data Processing, COM062000 - COMPUTERS / Data Modeling and Design, COM005030 - COMPUTERS / Enterprise Applications / Business Intelligence Tools
Publisher: Packt Publishing
Published: 2020-03-18T13:19:15+00:00

How to do it...

Finding full-time equivalent can be done in the following steps:

Create the following measure:

FTEs =

VAR __FT =

COUNTROWS(

SUMMARIZE(

FILTER('R05_Table', [Type] = "FT"),

[Employee]

)

)

VAR __PT =

SUMMARIZE(

FILTER('R05_Table', [Type] = "PT"),

[Employee],

"__Hours",SUM([Hours])

)

VAR __WorkHours =

SUMMARIZE(

'R05_Table',

[Date],

"__WorkHours",AVERAGE([Work Hours])

)

VAR __TotalPartTimeHours = SUMX(__PT,[__Hours])

VAR __TotalWorkHours = SUMX(__WorkHours,[__WorkHours])

RETURN

__FT + DIVIDE(__TotalPartTimeHours, __TotalWorkHours, 0)

Format FTEs to have two decimal places.

On a report page create a clustered column chart visualization and place the Date column from the R05_Table table into the Axis area of the visualization; also place the FTEs measure into the Value area of the visualization.

Use the forked arrow icon, Expand all down one level in the hierarchy, as well as the up arrow, Drill up, to drill down and up within the Date hierarchy and observe the varying values for the FTEs measure.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.