Need assistance on formula

[#1]

I have the follow pivot table.  For my final total, I need the difference between Revenue and Expenses.

When I ask for a subtotal by TYPE, (rev and exp) it adds the two totals together.

[eespiritu]

Hi Denise,

Have you tried add a new expression with the formula to obtain the difference? I don't know if is possible change the "Subtotal" or "Total" (QV default)  to other expression.

You can add a new expression with something like this:

sum(TOTAL <Dimension>[Revenue]) - sum(TOTAL <Dimension>[Expenses])

I hope that this can be useful.

Regards,

Enrique

[Anonymous]

The easiest way is to keep Expenses data negative.
An alternative, use conditional expression based on dimensionality, which is 0 for total:
if(dimensionality()=0, <expression for total>, <expression for rows>)

[#4 · Accepted solution]

Hi Denise

Probably you can do like this.

Create a formula for your value field depend on the type

Formula name(Variable) : eModValue

if(Type='Revenue',value,-1*value)

Call the formula in the pivot table expression.

sum($(eModValue))

In that way, Pivot table will subtract the values and gives you the expected answer.

Regards

Harshana

[#5]

Awesome!  This did it.

Thank you.

[#6]

Hi Denise

I am happy that is work you.

You can make that as a correct answer

Regards

Harshana