Variance and Percentage between MIN MAX Values

[robhel]

Hello PBI Gurus,

Need your assistance on how I can achieve the below be it formulars via Measures or Calculated Columns.  The cells on the left is what I have in my PBI model and the grey cells are what I am wanting to achieve with calculation based on filtered averages from the MAX between results eg - Region based on highest $s & Hrs = Variance 

Region = Blank is highest $s & Hrs, so the variance between all other regions

 

My PBI currently looks like the following:

Once again, greatly appreciate your support and assistance with this one

[TomMartens]

Hey, I would be lucky to help, but I have to admit that I do not fully understand what you are looking for. For this reason, please create sample data in Excel, create sample calculations. Upload the Excel file to onedrive or dropbox and share the link.

Regards,

Tom

[robhel]

Great thanks Tom, attached is spreadsheet - I am after a calculation that will provide the difference between the Regions based off the highest value returned  - RegionTechVariance

Please let me know if you have any further questions

[Ashish_Mathur]

Hi,

In range K3:K6, you have taken the denominator as 17138.  Where has this figure comes from?  Also, data in range B2:H7 looks like data which is already aggregated via formulas (this does not look like the base data).  Share the base data (which can be pasted in an Excel workbook) from where this table was built.

[robhel]

Thank you again for prompt response and yes, the fields are aggregated formulars based off the attached spreadsheet of the tables:

PMOData - PMO being unquie identifier

KEYTECH - Individual values from PMOData

REGION - Individual AREA from PMOData mapped to REGIONS

FY15 - Financial spend for FY15 with seperate tables for FY16, FY17, FY18 & FY19 - mapped by PMO identifier

PMOHrs - All hours booked against PMO from FY15 throu FY19

Cnt PMO = COUNTROWS(PMOData[PMO]

LTD $Spend = FY15[FY15 $] + FY16[FY16 $] + FY17[FY17 $] + FY18[FY18 $] + FY19[FY19 $]

Tlt Booked Hrs = SUM(PMOHrs[Hrs Worked]

Avg PMO LTD$ = [LTD $Spend] / [Cnt PMO]

Avg PMO LTDHrss = [CAL Tlt Booked Hrs] / [Cnt PMO]

And the number of 17,138 in the spreadsheet is the Cnt PMO total

https://www.dropbox.com/s/izslnqbk6rc7oqj/Tables.xlsx?dl=0

Greatly appreciated all your ongoing assistance - it really is invaluable

[Ashish_Mathur]

Hi,

I am not sure of how much i can help but i can defeinitely try.  Could you also share the link from where i can download your PBI file.