Aggregation like Pivot table in Excel

[dphigravity]

I know this might be a simple question but I am having a hard time finding the solution. In an example where I have fields State, Product, Sales Price, and Qty and I want to add calculated fields to my table (e.g. Revenue= Sales Price x Qty by Product), I can do that within one view. But if I want to see a visual on an aggregation of the Revenue by State, it would seem like I need to either create another view where I do a subquery or do some kind of derived table. Is this the only way to do this?

Thanks!

[blue1]

Hi dphigravity,

What if you add “Revenue” as a new measure to your Product view? In that way, you could freely reference it in whatever setting that you like, including pivoting the data by state and showing it as a Visualization.

Hope this helps,
Jasper

[dphigravity]

Thanks Jasper. What if my Revenue is based on Price (which is a dimension). When I created a new measure that transforms the Price Dimension to a Measure (numeric) in order to calculate Revenue (Price times Count), it seems like I need to include the original Price Dimension in my Data, otherwise I get an error. But I do not want to include Price as a dimension as that breaks up the aggregation view I wanted. Is there a way around this?

Failed to retrieve data - SELECT list expression references subscriptions.Price which is neither grouped nor aggregated at

[blue1]

Hi Dphigravity,

Let’s try to solve this by looking at the problem from a different perspective.

Revenue = Price * Quantity, right? Multiplication is essentially fancy summation, so we can also say that Revenue = Price + Price + Price …

You probably already have the price dimension below:

dimension: sale_price {
type: number
sql: ${TABLE}.Price ;;
}

Revenue can then be calculated by taking the sum of the price:

measure: total_revenue {
type: sum
sql: ${sale_price} ;;
}

This measure can then be used without the price being present in the query!

Hope this solves your issues,
Jasper

[dphigravity]

Thanks Jasper, your input made a huge difference!