Group measures Pivot Table

[johann_bauer]

Hi Guys,

im having a pivot table with 9 calculated Measures. I want to group these measures into 3 different groups, like in this picture.

I know that I have to add some Dimension or Measure to the Columns. But im not sure how I group them correctly. Because there is no logical relation between these measures

KR,

Johann

[#2]

There are different ways of tackling this, but the simplest (and assuming your data model isn't too large / complex, I would go with something like this - data islands can have impacts on caching and performance for very large applications, but I won't go into it here):

Load Script:

Sales:

Load * Inline [

Customer,RevenueMTD,RevenueYTD,ProfitMTD,ProfitYTD

X,10,100,1,11

Y,20,200,2,21

Z,30,300,3,31

];

Expressions:

Load * Inline [

ExpressionID,ExpressionGroup,ExpressionName

1,MTD,MTD Revenue

2,MTD,MTD Profit

3,YTD,YTD Revenue

4,YTD,YTD Profit

];

Chart Expression:

Pick(ExpressionID, Sum(RevenueMTD), Sum(ProfitYTD), Sum(RevenueYTD), Sum(ProfitYTD))

[johann_bauer]

Hi Graeme,

thanks for your reply.

Unfortunatly the calculated measures are very complex(Set analysis + variables) so I cant model them in my script.

[#4]

The logic for the expression above is not modelled in the script - just the name of the expression and it's grouping.

The logic for the expression is in the chart expression (you can include whatever set analysis you like in the chart expression):

Chart Expression:

Pick(ExpressionID, Sum(RevenueMTD), Sum(ProfitYTD), Sum(RevenueYTD), Sum(ProfitYTD))

[amit_shetty78]

Hi Graeme, thanks for this. Works nicely.

One issue is that when I collapse the expression group is there a way to do aggregation on the column included in the group? For e.g., sum all the values in the columns when collapsed.

thanks.

[eri]

@amit_shetty78 , I have the same question, can you please help?
I am able to do grouping using Expression inline code above. Thanks for sharing the code it works very well.

Is there any way to apply aggregation on ExpressionGroup? When I collapse the expression group in pivot table , the sum of all the values appears in the group name.
Please see screenshot for reference.

@johann_bauer @sunny_talwar 

#pivot #measuregrouping 

[eri]

Hi , could you please help me?
I have posted my question below in the thread

[eri]

Thanks to @sunny_talwar  for the solution which worked for me, the columns can  be grouped by using below expression-

Sum(Aggr(Pick(ExpressionID, Sum(RevenueMTD), Sum(ProfitMTD), Sum(RevenueYTD), Sum(ProfitYTD)), ExpressionID, Customer))
OR
Sum(Aggr(Pick(ExpressionID, Sum(RevenueMTD), Sum(ProfitMTD), Sum(RevenueYTD), Sum(ProfitYTD)), ExpressionGroup, ExpressionName, Customer))