Hi All,
Can someone help me on the below task, i calculated median, PIR = Submitted / median and average = PIR
But after doing above all calculations the average values are not matching exactly when we do the same in the excel.
Please find below link which has source excel files, testing excel results for Auckland location and PBIX for the data.
https://drive.google.com/drive/folders/1Iham304xMGAPks2XRZly_4EzQSdleDcm?usp=sharing
Data Source : Excel
Table Name : Market Data
Requirement : Calculating Average PIR where PIR = Submitted / Median and calculating these Averages for a given Career Level and Job Family.
Slicers : The 5 Market data filters are :
Location, Organisation Type, Industry, Head Count, Annual Revenue
Step 1:
When all filters are reset , Median is calculated on Submitted Column at a Job Code level .
Once filters are selected , Median is calculated on a Job Code and Particular filter selected .
For example , if Location is the filter that is selected ,then Median on Submitted column must be calculated at Job Code and Location level.
Similarly for every filter or combination of filters selected , then Median is calculated on Job Code + the column on which filter is applied.
Step 2 :
Create a new calculation PIR = Submitted/Median which is a row by row calculation .
Numerator = Submitted , which is considered only when [User Data] column is set to "Yes"
Denominator = Median on Submitted column , that changes based on slicer selections.
Step 3: Calculate the average on PIR column
Step 4 :
Display Averages in Matrix format , with rows as Career Level and Columns as Job Family and Average from step 3 as Values
