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!
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
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
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
Thanks Jasper, your input made a huge difference!
User | Count |
---|---|
4 | |
1 | |
1 | |
1 | |
1 |