topic Re: Group measures Pivot Table in App Development

Group measures Pivot Table

johann_bauer — Tue, 18 Jul 2017 07:27:03 GMT

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

Re: Group measures Pivot Table

Tue, 18 Jul 2017 08:14:45 GMT

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))

Re: Group measures Pivot Table

johann_bauer — Tue, 18 Jul 2017 10:04:34 GMT

Hi Graeme,

thanks for your reply.

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

Re: Group measures Pivot Table

Tue, 18 Jul 2017 11:21:07 GMT

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))

Re: Group measures Pivot Table

amit_shetty78 — Thu, 10 May 2018 11:20:26 GMT

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.

Re: Group measures Pivot Table

eri — Fri, 23 Oct 2020 16:40:28 GMT

@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

Re: Group measures Pivot Table

eri — Tue, 27 Oct 2020 13:24:52 GMT

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

Re: Group measures Pivot Table

eri — Wed, 28 Oct 2020 10:20:13 GMT

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))