Comparing current data with a baseline information which has no raw data source

[Anonymous]

Hi, 

I need to compare my current report with a baseline data which do not have raw data. My current report which is in a pivot (matrix) format is as per below. 

Basically, the above table is generated from three variables namely Main_Calc, Subs_Calc and TAT. And for TAT, Average was used. 

Now, I have to compare (substract) the above TAT (which is derived from current raw data)  with a baseline data which has no raw data  source as per below. 

I have to substract the current TAT with the baseline TAT (Current TAT - Baseline TAT) and generate a column namely "TAT Difference". I am not sure how to get this done as the there are different columns involved and there is no raw data for the baseline file. Would anyone be able to help me with a solution on this please? Been trying different methods for days but still unable to crack the solution. 

Thank you in advance. 

[amitchandak]

How you decide the baseline. In case it static number. You can add that to the table as a new column. If, this needs to be dynamic. You can create a what-if parameter (under modeling tab in Visualization/Report)

[ppodgorski]

Create a table 'Baseline' in query editor ("Enter Data") with your static baseline info. Columns <Subs_Calc> and <Tat>.

Relate the two tables together using <subs_calc> columns in each table. Then create a measure to calculate the difference.

[Anonymous]

Hi, 

I tried this method before but when I tried to create the measure to calculate the difference, I am unable not to aggregate the column TAT (from baseline data) on the measure.  Is there anyway to create a measure withoit having to aggregate the column, because I need the values for TAT(from baseline data) to be as is. 

[Anonymous]

Hi, 

I tried this method before but when I tried to create the measure to calculate the difference, I am unable not to aggregate the column TAT (from baseline data) on the measure.  Is there anyway to create a measure withoit having to aggregate the column, because I need the values for TAT(from baseline data) to be as is. 

[ppodgorski]

You can use SUM. As long as the baseline <subs_calc> value is unique, it will be be a SUM of 1 value.

[Anonymous]

Hi, 

When I use SUM, it does not present the numbers correctly either. The Subs_Calc is unique but when i use this formula 

TAT Difference = AVERAGE(FBL1N_JULY_WORKING[Invoice_Date_to_Submission_Date_Calc]) - SUM(Sheet1[TAT]) 

The results is incorrect. 

Sheet1 is basicaly the baseline data and Invoice_Date_to_Submission_Date_Calc is "TAT" column from current data.