Skip to Content
0
May 24, 2014 at 06:48 PM

Calculation of variance against other records

14 Views

Hi,

Wondering if the below is possible. If so, kindly advise.

Data as held in the cube:

Date Salesman Qty Sold 01/05/2014 S1 10 01/05/2014 S2 12 01/05/2014 S3 14 02/05/2014 S1 30 02/05/2014 S2 10 02/05/2014 S3 40 03/05/2014 S1 10 03/05/2014 S2 30 03/05/2014 S3 20

When the report is run for the above three days, the result is expected as below

Date Total Sold

Maximum

Sold

Minimum

Sold

Diff against Max

ABS(Max - Total Sold)

Diff against Min

ABS(Total Sold - Min)

01/05/2014 36 80 36 44 0 02/05/2014 80 80 36 0 44 03/05/2014 60 80 36 20 24

I was able to produce the columns as far as Maximum & Minimum. These were acheived by "Calculating single value as" MAXIMUM or MINIMUM. However facing difficulty in calculating Diff Max & Diff Min. The normal Formula builder doesn't seem to help here.

Appreciate suggestions/thoughts.

Thanks,

Ram