5 Replies Latest reply on Jul 15, 2015 1:14 AM by Felix Curran

# How to generate a report with splitting the different values in one dimension with calculation?

I have the data set like

 Product Name Product Status Create Date P1 Ordered D1 P1 Cancelled D2 P1 Ordered D3 P1 Delivery D4 P2 Ordered D5 P2 Delivery D6 P2 Received D7 P3 Ordered D8 P3 Cancelled D9 P4 Ordered D10 P4 Delivery D11 P3 Ordered D12 P4 Ordered D13 P4 Delivery D14 P4 Received D15 P1 Delivery D16 P2 Ordered D17 P1 Delivery D18 P2 Cancelled D19 P3 Ordered D20 P4 Ordered D21

And I want to use tableau to finally get such display with calculating cancel / Order rate

 Product Name Cancelled Delivery Ordered Received COR P1 1 3 2 50% P2 1 1 2 1 50% P3 1 3 33% P4 2 3 1 0%

Any idea?

• ###### 1. Re: How to generate a report with splitting the different values in one dimension with calculation?

Hi Yuan,

Have a look at the attached .twbx, I think it covers everything you need.

If you need any of it explained let me know.

Regards,

Felix

1 of 1 people found this helpful
• ###### 2. Re: How to generate a report with splitting the different values in one dimension with calculation?

Or you can create one formula for COR by keeping your data structure the same:

TOTAL(SUM(IF [Product Status]='Cancelled' THEN 1 else 0 end)) / TOTAL(SUM(IF [Product Status]='Ordered' THEN 1 else 0 end))

Set it as a discrete (blue) pill on rows with compute using set to table(across).

Note: setting it to discrete will have impact on grand totals and like.

• ###### 3. Re: How to generate a report with splitting the different values in one dimension with calculation?

Thank you, Felix. It is exactly what I want. But could you elaborate more on how to do the multiple aggregations, and what's the rational using fake calculation field? Thank you.

• ###### 4. Re: How to generate a report with splitting the different values in one dimension with calculation?

It is fantastic, Pooja. Thank you! It does work!

• ###### 5. Re: How to generate a report with splitting the different values in one dimension with calculation?

Hi Yuan,

Each of those measures I created simply say "IF Product Status = 'thing' THEN 1 ELSE 0 END", these are then summed to give the total of each status for each product name.

I guess they should probably be called fake column, as that better describes what they're trying to do. By setting them as MIN(1) and bringing them into the column shelf we can ensure that they will all be bars with a value of 1. Making these bars fully transparent and then putting a measure in as the label of the field allows us to create a table of multiple columns, which is something that is usually troublesome with Tableau.

However, for this particular example, Pooja has a better solution.

Regards,

Felix