You can do this with The Aggregate Step in Tableau Prep. It's also described in more detail here: Data Prep 101: What is an aggregate function and how do you combine aggregated data? | Tableau Software. When you use it, you can specify different aggregations for each measure:
CNT is used for Transaction ID, for example. Discount could have a different one after you drag it, like AVG.
Hope this helps!
Thank you for your reply!
I know the common aggregation approach, but it is not what I exactly need.
Actually, Fact Name is Dimension and Fact Value is Measure:
I need a different type of aggregation for each type of Fact Name: i.g SUM for Value and Units, AVG for Distribution etc.
Literally, I need to unpivot the table to make each Fact Name in separate column, aggregate them separately and pivot back again.
Is it possible to perform in Tableau Prep?
1 of 1 people found this helpful
Ah, unpivot I think it's the keyword. In Tableau Prep you have been able to pivot columns to rows from version 1, but starting earlier this year you can also pivot rows to columns (New in Tableau Prep: Pivot rows to columns and add descriptions to any change | Tableau Software) and I think that would solve it.
What I'd do is separate the data set in 2 branches (or more, if you have more than 2 types of aggregations), then filter the data on each branch for just the corresponding facts. After that, you can pivot rows to columns (or unpivot) each branch (I'm aggregating on the example below, but in your case you may want to add a regular aggregation step after this unpivot):
After this, you would union the 2 branches, and you could pivot (colums to rows) back again if you'd like.
I would give this a try with your data. Good luck!
Thanks for your help! The approach is quite clear but can you please suggest: how to specify the aggregation of unpivoted fields by weeks?
I need to keep years and months levels but weeks should be aggregated by SUM and by AVG respectively.
Thank you for your suggestion - I've managed with two types of aggregation by weeks with my data.