cancel
Showing results for
Did you mean: Frequent Visitor

## Multiple KPI Calculations over a KPI Table - options for fast performance

Hello!

I have to calculate multiple KPI's based on a KPI table that relates to a transactional table with amounts (over 800k recods, with AC FC PL PY switch, and MTD/YTD and Currency Switches). Basically each KPI refers to a group of transactions, at the same tome they can have various calculations conditions (eg. filtering other dimensions).

I tried various options but not able to get the performance i would expect.

Right now the best option is to use SUMX over the KPI table and SWITCH on each encounter of a KPI and apply the formula. The KPI formulas are calculations between the KPI's themself (eg DIVIDE(KPI_A,KPI_C).

Is there a better way to approach this situation and have a better DAX logic for the measure(s) that will also provide fast percormance? I tried with multiple calculate and switch measures, but performance is worse.

Current performance of measure using a matrix visual in PowerBI (query extracted and runed in DAX Studio): Below current measure I use:

``````MEASURE ' Measures'[__mCalculateKPIGroups] =

VAR __vReturn =
SUMX (
//Filter KPI table for Management P&L Hierarchies
FILTER('KPI Groups',SELECTEDVALUE ('KPI Groups'[LEV1_Name]) = "AAA" || SELECTEDVALUE ('KPI Groups'[LEV2_Name])="BBB"),

//Get row context
VAR __vKPI_ID							= 'KPI Groups'[KPIGroup_SID]
VAR __vKPI_LEV3_NAME					= 'KPI Groups'[LEV3_Name]
VAR __vKPI_LEV2_NAME					= 'KPI Groups'[LEV2_Name]
VAR __vKPI_LEV1_NAME					= 'KPI Groups'[LEV1_Name]

VAR __vGetTimePeriod					= SELECTEDVALUE ( 'Time Period Selector'[Period])
VAR __vGetKPI_Formula					= LASTNONBLANK(SELECTCOLUMNS(RELATEDTABLE('KPI Groups Details'),"Formula",[Formula]),TRUE())
VAR __vGetDataType						= SELECTEDVALUE('Data Type'[Data Type])

//Filtering conditions
VAR __vFilterCondCATDefault				= {"a","b","c"}
VAR __vFilterCondCATINPL				= "z"
VAR __vFilterCondCATINAC				= "x"
VAR __vFilterCondCATFT_PL				= "y"
VAR __vFilterIA							= FILTER(VALUES('ID'[DEP]),NOT('ID'[DEP]=BLANK()))
VAR __vFilterID							= FILTER(VALUES('ID'[DEP]),'ID'[DEP]=BLANK())
VAR __vFilterOU							= FILTER(VALUES('OA'[NAME]),NOT([NAME] IN {BLANK(),"INF"}))
VAR __vFilterCATDefault					= FILTER(VALUES('Category'[Category]),[Category] IN __vFilterCondCATDefault)
VAR __vFilterCATINPL					= FILTER(VALUES('Category'[Category]),[Category] = __vFilterCondCATINPL)
VAR __vFilterCATINAC					= FILTER(VALUES('Category'[Category]),[Category] = __vFilterCondCATINAC)
VAR __vFilterCATFT_PL					= FILTER(VALUES('Category'[Category]),[Category] = __vFilterCondCATFT_PL)
VAR __vFilterACCFT_MTD					= FILTER(VALUES('ACC'[ACC]),NOT(CONTAINSSTRING('ACC'[ACC],"AVG")))
VAR __vFilterACCFT_YTD					= FILTER(VALUES('ACC'[ACC]),    CONTAINSSTRING('ACC'[ACC],"AVG"))

//Calculate KPI Groups
VAR __vCalculateIN_AC					= CALCULATE ([__mSwitchSign],__vFilterCATINAC,__vFilterIN)
VAR __vCalculateIN_PL					= CALCULATE ([__mSwitchSign],__vFilterCATINPL,__vFilterIN)
VAR __vCalculateIN						= IF(__vGetDataType  IN {"PL"},__vCalculateIN_PL,__vCalculateIN_AC)
VAR __vCalculateFT_MTD					= CALCULATE(-[__mSwitchSign],__vFilterACCFT_MTD,__vFilterCATDefault )
VAR __vCalculateFT_YTD					= CALCULATE(-[__mSwitchSign],__vFilterACCFT_YTD,__vFilterCATDefault)
VAR __vCalculateFT						= IF(__vGetTimePeriod = "MTD",__vCalculateFT_MTD,__vCalculateFT_YTD)

//Switch Conditions for KPI Groups
VAR __vSwitchOnINGroups_MN				= __vKPI_LEV1_NAME="AAA" && __vKPI_LEV2_NAME="CCCCC" && __vGetDataType  IN {"AC","PL"}
VAR __vSwitchOnINGroups_TG				= __vKPI_LEV1_NAME="AA" && __vKPI_LEV2_NAME="BBB" && AND(__vKPI_ID>=380,__vKPI_ID<=398) && __vGetDataType  IN {"AC","PL"}
VAR __vSwitchOnFT						= CONTAINSSTRING(__vKPI_LEV3_NAME,"FT")
VAR __vSwitchOnFT_IN					= CONTAINSSTRING(__vKPI_LEV3_NAME,"FT*AD") ||  CONTAINSSTRING(__vKPI_LEV3_NAME,"FT*INT")
VAR	__vSwitchOnSignChange				= CONTAINSSTRING(__vGetKPI_Formula,"-*{C,") && NOT(CONTAINSSTRING(__vGetKPI_Formula,"+")) && LEFT(__vGetKPI_Formula,1)="-"

//Switch Conditions for KPI Formulas
VAR __vSwitchOn_NRPS					= __vKPI_LEV3_NAME="Net Revenue Professional Services"
VAR __vSwitchOn_SoftNR					= __vKPI_LEV3_NAME="Software NR of Total NR"
VAR __vSwitchOn_ProdRATE				= __vKPI_LEV3_NAME="Production Rate"
VAR __vSwitchOn_UtilRATE				= __vKPI_LEV3_NAME="Utilization Rate"
VAR __vSwitchOn_NRPH					= __vKPI_LEV3_NAME="Net Rate per Hour"
VAR __vSwitchOn_SalCFS					= __vKPI_LEV3_NAME="Salary of CFS FT p.m."
VAR __vSwitchOn_EBIT_Margin				= __vKPI_LEV3_NAME="EBIT margin % (pre investments)"
VAR __vSwitchOn_EBITImpact				= __vKPI_LEV3_NAME="Investments (EBIT impact)"
VAR __vSwitchOn_EBITinv					= __vKPI_LEV3_NAME="EBIT incl. Investments"

//Calculate KPI's
// [Net Revenue Professional Services] = [Net Revenues] - [Net Revenue Licenses, Revenue Licenses] - [Net Revenue Recurring Software, Revenue Recurring Software] - [Net Revenue Managed Services, Revenue Managed Services]
VAR __vCalculateKPI_NRPS				= CALCULATE([__mSwitchSign], REMOVEFILTERS('KPI Groups'),__vFilterIndustry,__vFilterOU,'KPI Groups'[LEV3_Name] = "Net Revenues")
- CALCULATE([__mSwitchSign], REMOVEFILTERS('KPI Groups'),__vFilterIndustry,__vFilterOU, 'KPI Groups'[LEV3_Name] = "Net Revenue Licenses, Revenue Licenses")
- CALCULATE([__mSwitchSign], REMOVEFILTERS('KPI Groups'),__vFilterIndustry,__vFilterOU, 'KPI Groups'[LEV3_Name] = "Net Revenue Recurring Software, Revenue Recurring Software")
- CALCULATE([__mSwitchSign], REMOVEFILTERS('KPI Groups'),__vFilterIndustry,__vFilterOU, 'KPI Groups'[LEV3_Name] = "Net Revenue Managed Services, Revenue Managed Services")

// [Production Rate] = [Net Available Hours - Professionals] / [Available Hours]
VAR __vCalculateKPI_ProdRATE			= DIVIDE(CALCULATE(-[__mSwitchSign], REMOVEFILTERS('KPI Groups'),__vFilterIndustry,__vFilterOU,'KPI Groups'[LEV3_Name] = "Net Available Hours - Professionals")
, CALCULATE(-[__mSwitchSign], REMOVEFILTERS('KPI Groups'),__vFilterIndustry,__vFilterOU,'KPI Groups'[LEV3_Name] = "Available Hours"))

// [Utilization Rate] = [Total Chargeable Hours] / [Net Available Hours - Professionals]
VAR __vCalculateKPI_UtilRATE			= DIVIDE(CALCULATE([__mSwitchSign], REMOVEFILTERS('KPI Groups'),__vFilterIndustry,__vFilterOU,'KPI Groups'[LEV3_Name] = "Total Chargeable Hours")
, CALCULATE([__mSwitchSign], REMOVEFILTERS('KPI Groups'),__vFilterIndustry,__vFilterOU,'KPI Groups'[LEV3_Name] = "Net Available Hours - Professionals"))

//Default calculation
VAR __vCalculateDefault					= CALCULATE([__mSwitchSign],__vFilterIndustry,__vFilterOU,__vFilterCATDefault)

RETURN
SWITCH(TRUE(),
// Switch for IN Calculation
__vSwitchOnINGroups_MN				,__vCalculateIN,
__vSwitchOnINGroups_TG				,__vCalculateIN,

// Switch for FT Calculation
__vSwitchOnFT						,__vCalculateFT,

// Switch for KPI Formulas Calculation
__vSwitchOn_NRPS					,__vCalculateKPI_NRPS,
__vSwitchOn_ProdRATE				,__vCalculateKPI_ProdRATE,
__vSwitchOn_UtilRATE				,__vCalculateKPI_UtilRATE,
__vSwitchOn_NRPH					,__vCalculateKPI_NRPH,

// Change sign for special KPI's
__vSwitchOnSignChange				,-__vCalculateDefault,

// If no other conditions then return default calculation
__vCalculateDefault

)

)
RETURN
SWITCH (
TRUE (),
// Remove calc for levels 1 and 2
ISINSCOPE ( 'KPI Groups'[LEV3_Name] )  || ISFILTERED ( 'KPI Groups'[LEV3_Name] ), __vReturn,
ISINSCOPE ( 'KPI Groups'[LEV2_Name] )  , BLANK(),
ISINSCOPE ( 'KPI Groups'[LEV1_Name] )  , BLANK(),
BLANK()
)``````  Super User

@cbogdanmihai , I think you should consider calculation groups or field parameters

Calculation Groups- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display : https://youtu.be/qMNv67P8Go0 Announcements #### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023. #### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers! #### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February. #### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders. Top Solution Authors
Top Kudoed Authors
Users online (4,638)