5 Replies Latest reply on May 23, 2016 8:00 AM by Joe Oppelt

Hi,

I am writing a simple case formula to calculate the margin ..

CASE [CURRENCY] WHEN "USD"

THEN

SUM([TOTAL SALES]-[TOTAL COST])/SUM([TOTAL SALES])

WHEN "EURO"

THEN

SUM([TOTAL SALES IN EUROS] - [TOTAL COST])/SUM([TOTAL SALES])

END

i am getting the error: can't mix between aggregate and non aggregate ..

I tried to change  to

CASE ATTR([CURRENCY]) WHEN 'USD' ...

but the results are not correct ..

I've attached a sample workbook

Message was edited by: Rabeeh El Hussein

Try MIN(Currency) or MAX() or AVG()

Just a guess,

This may involve how this is set up on the sheet.  When you say, "not correct", that doesn't help much.

I would want to play with an actual working example to understand what's going on in your workbook.

Consider anonymizing your data to make a subset of what you have.  We would just need a few rows, and the one sheet.  Follow the guidelines in this short video to anonymize your stuff:

1 of 1 people found this helpful

SUM(

CASE [Currency]

WHEN "USD" then ([Total Sales]-[Total Cost])/[Total Sales]

WHEN "EURO" then ([Total Sales In Euros]-[Total Cost])/[Total Sales In Euros]

END

)

I tried the proposed solution .. it didn't work out  .. I attached a sample workbook .. Hope you can help.

I've attached a sample workbook .. Thanks.

(Note to self:  Tableau 9.3)

Well I just looked at this.  None of your fields in your example match what you have in your CASE statement in your initial post.

CASE [CURRENCY] WHEN "USD"

THEN

SUM([TOTAL SALES]-[TOTAL COST])/SUM([TOTAL SALES])

WHEN "EURO"

THEN

SUM([TOTAL SALES IN EUROS] - [TOTAL COST])/SUM([TOTAL SALES])

END

I substituted [Gross Sales] for both [Total Sales] and [Total Sales in Euros], and I substituted [Cost] for [Total Costs].

To eliminate the agg/non-agg error I put [Currency] in ATTR().

In your first post you said you tried that but your results were "not correct".  I asked what "not correct" means.

Given the sample workbook you provided, what should I be aiming for?