I want to use a pivot and add some measures ignoring the pivot column.
I have data something similar to below.
In the above example i have three metrics.
I need to create a data report similar to below.
Sales need to be aggregated per department and cost and quantity need to be shown at item level in a same view. If i take all there three metrics and pivot on Itemsold, I can see the three metrics at item and department level, but i want to bypass item level aggregation for sales and show only at Department level. To achieve this i might have to create a custom dimension but still aggregates at dpeartment level.