Group Kpis by theme

[GillBech]

Hello

Is there a way in a pivot table to group measures by group (eg like in the picture below)

Thank you

[edwin]

you need two dimension fields one for the name of the metric and another for the grouping.  you can implement it either in the script where the actual metric value is a single field and you associate it to the metric field or if your measures are in separate fields, you implement the logic in the chart as a single measure and your expression computes for the value based on the metric.  the first option is faster of course. 

[vinieme12]

Create a  inline table as below , make sure this DOESN'T associate with any other table in your model

Kpigroups:

Load * inline [ 

KpiGroup,so_KpiGroup,KPI,so_Kpi

Turnover,1,Amount,1

Turnover,1, Evolution,2

Margin,2,Amount,3

Margin,2,% ofTurnover,4

Contracts,3,NB,5

Contracts,3,NB new contracts,6

];

In your pivot table,

Add Row Dimensions

KpiGroup,KPI

Sort KpiGroup by measure =max(so_KpiGroup)

Sort KPI by measure

=max(so_KPI)

Measure

=Pick(so_KPI,

, Expression for Amount(turnover)

, Expression for Evolution

, Expression for Margin amount

, Expression for % of turnover 

, Expression for NB 

, Expression for NB new contracts

)  

<<< Basically Enter the expressions in the same order as the sorting order for each kpi as defined in the inline table