cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cbogdanmihai
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):

cbogdanmihai_1-1666094672716.png

 

 

 

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

 

 

1 REPLY 1
amitchandak
Super User
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

 

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

 

Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

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