Calculated Difference between measures at chart level

[ghaliabed]

Hello

We have a report where there is a need to add a measure that will always calculate difference between the columns before it.  Similar to below table

	Actual	Budget	Var
Measure1	10	4	6
Measure2	5	5	0
Measure3	6	4	2

This could be done using Column(1) - Column(2) in theory.

However if you notice here i have a Dimension that has {'Actual', 'Budget'} and i have three separate Measures, so Column(1) - Column(2) will only give me difference between the measures for the same value of the Dimension.

Is there a way to do this in Sense ?

[ghaliabed]

From my understanding this type of behavior was present in QlikView ?
Where you could use the Column label as a reference and use it in your expressions

[kaanerisen]

Hi,

You can use valuelist to create custom columns.

Dimension:

=ValueList('Actaul','Budget','Var')

Measure:

Pick(Match(ValueList('Actual','Budget','Var'),'Actual','Budget','Var'),
Sum({<TYPE={'Actual'}>}VALUE),
Sum({<TYPE={'Budget'}>}VALUE),
Sum({<TYPE={'Budget'}>}VALUE)-Sum({<TYPE={'Actual'}>}VALUE)
)

Hope it helps.

[ghaliabed]

hi thanks for the post

This helps to a certain point but still facing some issues:

• If i do not put a second measure the row stays blank for some reason:

with only one measure defined

With Two Measures shows correctly

• With this approach i will need to create a new Var measure for each measure already in my model
  • As in if i have Sales & Cost & Revenue & HeadCount as measures, i will need to have new [Sales with Var], [Cost with Var], [Revenue with Var], [Head Count with Var]
  • This will make it harder for self-service or maintenance in the long term

Ideally, if there is a way to do as was present in QLikView, where you can just reference the Column named Actual & Budget and get difference between them.