Hi Paul

Is your example meant to reflect what you actually expect?

I only see 1 X entry for example?!

Something like this may work for you (its using LoD, you can tell by the use of { } )

X Count >> IF [Product]='x' THEN {FIXED [Store ID] : COUNTD([Number of Records])} ELSE NULL END

Non X Count >> IF [Product]<>'x' THEN {FIXED [Store ID] : COUNTD([Number of Records])} ELSE NULL END

Example attached, v9.1

Cheers

Mark

Sorry Paul, it's me, but I don't quite understand

... StoreID 1- If an X is present, then count only 1 X, and 0 non X? So it should look like this?

Total are

X Count =  1         

Non X count = 5

Or something different?!

Cheers

Mark

OK, here with attempt 2...

You can display the X Count or not, but we do need it calculated as its used in the non-X count.

X Count >> IF [Product]='x' THEN {FIXED [Store ID] : COUNTD([Number of Records])} ELSE NULL END

+

Non X Count >> IF [Product]='x' THEN {FIXED [Store ID]: SUM([Number of Records])-SUM([X Count])} ELSE 0 END

v9.1 attached.

Cheers

Mark

Hi Paul!

That would be a single calculation like this:

{FIXED [Store ID]: MAX(IF [Product] = 'x' then 'with x' else 'not with x' END )}

Drag this calculation on columns, product on rows and number of records on text:

Result:

We'll just wait till Pooja turns up... oh wait...!

Great work Pooja Gandhi

To try and salvage something from this...

If by any chance you have to show 0 rather than leave them blank, swap Number of Records for this new calculated field > ZN(LOOKUP(SUM([Number of Records]),0))

Assuming all is well, please mark Pooja's answer correct

Cheers

Mark

Oh yes! I forgot about the **** zeros

Good catch, Mark!

Mark Fraser can you attach me the work book so i can see what you mean with the zeros?

of course, please find attached v9.1

Paul,

While using LOD expressions, the result need to be an aggregate. You could use aggregation as it relates to the result within the IF statement. Here the result was a string data type because we stated 'with x' and 'not with x' and hence used MAX().

So that tells Tableau that at a FIXED store level, if there are products with 'x' then show 'with x' for all products within that store. If you didn't use LOD expressions and instead used a general IF statement, 'with x' would be the output for only those rows where product is a 'x'.

Something like below, calculation 2 would've shown 'with x' only for the row where product is 'x'. But since you wanted to count the records for all products and not just product 'x', we used LOD versus using the general IF statement.

Makes sense?

Ok lets look at it a little differently. Lets change the formula to:

Calculation1: IF [Product] = 'x' then 1 else 0 end

This will throw a 1 for all rows where product = 'x' so it looks like this:

Notice there is a 0 for the row where product = 'y'.

Calculation2: { fixed [Store ID] : MAX([Calculation1 ]) } so it looks like this:

MAX of calculation 1 at a fixed store level is 1 for store id 1. So it will fill all rows for that store with a 1. You need a 1 for the row where product = 'y' because you are counting the number of times all products that appear where product = 'x'.

So same concept with the text 'with x' and 'not with x'. In the example above, 1 = with x and 0 = 'not with x'. and MAX() for the strings in LOD would work the same way. Does that make sense now?