KPI based on previous
I want to calculate with a DAX formula the Target of my KPI based on the filter of the date of the same month, but of the previous year and multiply by 2. FOR EXAMPLE, if I have in January 2020 count a total number of 100 for YourTable[date] as the number of rows (i.e. count the total number of rows of the YourTable[date] column corresponding to the period January 2020), then my Target for January 2021 should return 200. If I have in 2020-01-01, 1000 as the number of rows of YourTable[date], then this should return me 2000 for 2021-01-01, as my Target. So, each time, my Target must take into account the number of rows for the same period and multiply it by 2. I use a Slicer to filter my dates. So, if I just select 2019 as the year and it returns 10000 as the number of rows, then my target should return 20000.
KPI - Red if 3 month in a row
I have data about project progress. I am trying to make a KPI for project that allows project to be yellow or red for max 3 month. After that I want a column to counts 1. Columns I got: "Projectid": The uniq ID for projects (Hidden of cause) "Status": 1 if green, 2 if yellow, 3 if red "Count of Red": 1 if red else 0 "Count of Yellow" 1 if yellow else 0 "Count of Green" 1 if green else 0 "Month" and "Year - month": The same month apear once for each project. The tabel register the given status for each month (Look at the picture):
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] //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() )
Seeking help to recreate a table/card
Hi Community, I am trying to recreate a--very simple--scorecard for a client. This client creates a scorecard in Excel and I am attempting to recreate, verbatim, what they have created. I am struggling to find what I should use. I have tried a table, matrix and a kpi tile to no avail. Can anyone help guide me to what visual would be best to provide the below? Thank you!
