CODEWITHSUNDEEP
aggregate-functionstableau-api
Logan Wilson
Logan Wilson

Reputation: 3

How can I can aggregate by group over an aggregate in Tableau?

I'm trying to visualize the median profit as a proportion of sales for each day of the week. My data looks like this:

Date Category Profit Sales State
1/1  Book     3      6     NY
1/1  Toys     12     30    CA
1/2  Games    9      20    NY
1/2  Books    5      10    WA

I've created a calculated field "Profit_Prop" as SUM([Profit])/SUM([Sales]). I want to display the median daily value of profit_prop for Mondays, Tuesdays, etc.

I can kind of do this as a boxplot by adding WEEKDAY(Date) to Columns and Profit_Prop to Rows, then adding Date to Detail and changing granularity to Exact Date. But I just want to display the median without displaying a data point for each day.

I tried making another calculated field with MEDIAN([Profit_prop]), but I get "argument to MEDIAN is already an aggregation and cannot be further aggregated."

Upvotes: 0

Views: 2538

Answers (2)

Think
Think

Reputation: 36

Remove date from the level of detail. Create calculated field like below and use it instead of Profit prop

median(
{ INCLUDE [Date]:
[Profit_Prop]
}
)

Let me know how it goes.

Upvotes: 1

Siva
Siva

Reputation: 9101

When you are doing a calculation on a calculated field normal median function doesn't work instead you need to use the Table calculations.

Taking data from your example, create a formula. Use below code:

Create a calculated field and paste below code:

WINDOW_MEDIAN([Calculation1],FIRST(),LAST())

Set the computation to Table Down

Upvotes: 0

Related Questions