3 Replies Latest reply on Feb 15, 2018 11:14 AM by Michel Caissie

# Formatting Question with multiple calculated measures

I have a simple problem I can't seem to figure out, maybe someone knows an easy solution?

I am trying to show part 1

- Actual sales prior year

- Budget current year

- Actual sales current year

- and Actual sales + open as a dotted line reference line

When I add the reference line in my current setup it goes all the way across all the measure names, no big deal, but is there a simple way to have it only show over actuals 18? or at least only show the label one time and not 3?

I am trying to show part 2

- Actual sales prior year

- Budget current year

- Actual sales current year

- and Actual sales + open  as a two color bar below

Ideally this looks the same as above but I would be able to make the open portion of the open + actual a slightly different blue

• ###### 1. Re: Formatting Question with multiple calculated measures

Robin,

For the color, you can make the following calc and put it on the color shelf

if

[Sales Type] = 'actual'

AND

YEAR([Year])=2017

THEN

'Actual 17'

ELSE [Sales Type]

END

For the reference line,  I don't know if it is possible to show it on a single cell,  but  you can set the scope to Entire table  and format the label alignment to right.

To get the proper value you will need to use this calculation

SUM( if

[Sales Type]  = 'open'

AND

YEAR([Year]) =2018

THEN

[Value]

ELSE 0

END )

+

SUM( if

[Sales Type]  = 'actual'

AND

YEAR([Year]) =2018

THEN

[Value]

ELSE 0

END )

Michel

• ###### 2. Re: Formatting Question with multiple calculated measures

Great,

Thanks for the help on that, I was really close on the color, but I hadn't figured out the reference line.

it's a little strange it pushes the line across the whole table when the values are null, maybe because it's based on the axis?

cheers,

• ###### 3. Re: Formatting Question with multiple calculated measures

I though that it would be possible to set it by cell and get a null line on the 2 other measures but it doesn't seems to work.

Probably related to the fact that we have  Measure Names on the columns  and not a Dimension.