Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
johann_bauer
Partner - Contributor III
Partner - Contributor III

Group measures Pivot Table

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

Pivot.png

KR,

Johann

7 Replies
Not applicable

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

18-07-2017 10-10-26.jpg

johann_bauer
Partner - Contributor III
Partner - Contributor III
Author

Hi Graeme,

thanks for your reply.

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

Not applicable

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
Creator II
Creator II

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
Contributor III
Contributor III

@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.

eri_0-1603471172819.png

 

@johann_bauer @sunny_talwar 

#pivot #measuregrouping 

eri
Contributor III
Contributor III

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

eri
Contributor III
Contributor III

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