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

[cbogdanmihai]

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]
		
		//Get additional attributes
		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()
)

[amitchandak]

@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

Field parameter - https://amitchandak.medium.com/power-bi-field-parameters-a-quick-way-for-dynamic-visuals-fc4095ae9af...