Forum Discussion

robhel's avatar
robhel
Helper I
8 years ago

Variance and Percentage between MIN MAX Values

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 a...
Need Help
Show and Tell
Tips and Tricks
robhel's avatar
robhel
Helper I
8 years ago

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's avatar
Ashish_Mathur
Super User
8 years ago

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.