Need help with my measure

[abanar2]

Hello Community, 

I’m currently working on a project where I need to calculate Key Performance Indicators (KPIs) using a DAX measure, but I’m having some difficulties and could use your help.

I have a dataset with the following columns:

Column Name	Meaning
Year	Year of the data row
Quarter	Quarter of the data row
Site_ID	Site_ID of the data row
KPI_ID	KPI_ID of the data row
KPI_Name	KPI_Name of the data row
Is_Total_KPI	Yes: the selected KPI used as itself as KPI Group
KPI_Group	Group of the KPIs
KPI_Value	The value for the selected year, quarter, site and KPI
Dividend_ID	The ID of the KPI what we use as dividend for in the KPI Group calculation
Divisor_ID	The ID of the KPI what we use as divisor for in the KPI Group calculation

I need to create a measure that can handle three different calculation scenarios:

1. When we calculate a division between KPI pairs (e.g., KPI2 / KPI1)
2. When we want to use a KPI, which is already a member of a KPI Group, as a KPI Group in itself (e.g., KPI1)
3. When our KPI isn’t a member of any calculation but we evaluate it as itself (e.g., KPI3)

Ultimately, I want to visualize the KPI_Group_Values with the Year, Quarter, Site_ID, and KPI_Group_Name in a table or matrix.

I will paste a data sample into the comments.

At the end I would like to visualize the KPI_Group_Values with the Year, the Quarter, the Site_ID, The KPI_Group_Name in a table or in a matrix. I calculated the aim KPI_Group_Values into a table what you can find above:

Year Quarter Site_ID KPI_Group KPI_Group_Value

2021	1	Site1	Training Planned	79
2021	2	Site1	Training Planned	75
2021	3	Site1	Training Planned	67
2021	4	Site1	Training Planned	77
2021	1	Site1	% Training Done during the Quarter	16.46%
2021	2	Site1	% Training Done during the Quarter	21.33%
2021	3	Site1	% Training Done during the Quarter	26.87%
2021	4	Site1	% Training Done during the Quarter	36.36%
2021	1	Site1	Number of Focus Rooms in the Office	100
2021	2	Site1	Number of Focus Rooms in the Office	100
2021	3	Site1	Number of Focus Rooms in the Office	100
2021	4	Site1	Number of Focus Rooms in the Office	100
2021	1	Site2	Training Planned	34
2021	2	Site2	Training Planned	25
2021	3	Site2	Training Planned	73
2021	4	Site2	Training Planned	10
2021	1	Site2	% Training Done during the Quarter	235.29%
2021	2	Site2	% Training Done during the Quarter	136.00%
2021	3	Site2	% Training Done during the Quarter	102.74%
2021	4	Site2	% Training Done during the Quarter	180.00%
2021	1	Site2	Number of Focus Rooms in the Office	70
2021	2	Site2	Number of Focus Rooms in the Office	70
2021	3	Site2	Number of Focus Rooms in the Office	70
2021	4	Site2	Number of Focus Rooms in the Office	70
2022	1	Site1	Training Planned	33
2022	2	Site1	Training Planned	71
2022	3	Site1	Training Planned	11
2022	4	Site1	Training Planned	49
2022	1	Site1	% Training Done during the Quarter	109.09%
2022	2	Site1	% Training Done during the Quarter	123.94%
2022	3	Site1	% Training Done during the Quarter	318.18%
2022	4	Site1	% Training Done during the Quarter	179.59%
2022	1	Site1	Number of Focus Rooms in the Office	90
2022	2	Site1	Number of Focus Rooms in the Office	90
2022	3	Site1	Number of Focus Rooms in the Office	90
2022	4	Site1	Number of Focus Rooms in the Office	110
2022	1	Site2	Training Planned	67
2022	2	Site2	Training Planned	96
2022	3	Site2	Training Planned	29
2022	4	Site2	Training Planned	83
2022	1	Site2	% Training Done during the Quarter	73.13%
2022	2	Site2	% Training Done during the Quarter	34.38%
2022	3	Site2	% Training Done during the Quarter	331.03%
2022	4	Site2	% Training Done during the Quarter	32.53%
2022	1	Site2	Number of Focus Rooms in the Office	70
2022	2	Site2	Number of Focus Rooms in the Office	70
2022	3	Site2	Number of Focus Rooms in the Office	70
2022	4	Site2	Number of Focus Rooms in the Office	72

[v-tangjie-msft]

Hi @abanar2 ,

Are the numbers in your post the expected results you want? If so, can you give an example of how to get this line of results in conjunction with your example data (REPLY 2)?

2021

1

Site1

% Training Done during the Quarter

16.46%

Best Regards,

Neeko Tang

[abanar2]

2021

1

Site1

% Training Done during the Quarter

16.46%

= 

Dividend row:

Year	Quarter	Site_ID	KPI_ID	KPI_Name	Is_Total_KPI	KPI_Group	KPI_Value	Dividend_ID	Divisor_ID
2021	1	Site1	KPI1	Training Planned	Yes	% Training Done during the Quarter	79	KPI2	KPI1

Divisor row:

2021

1

Site1

KPI2

Training Done

No

% Training Done during the Quarter

13

KPI2

KPI1

13 / 79 = 0.164556962 

[v-tangjie-msft]

Hi @abanar2 ,

We can create two tables.

Table 2 = FILTER(SELECTCOLUMNS('Table',[Year],[Quarter],[Site_ID],[KPI_Name],[KPI_Value]),[KPI_Name] IN {"Training Planned","Number of Focus Rooms in the Office"})

Table 3 = 
var _a=DISTINCT(SELECTCOLUMNS('Table',[Year],[Quarter],[Site_ID]))
var _b={"% Training Done during the Quarter"}
RETURN CROSSJOIN(_a,_b)

We create a calculated column on Table 3.

Table_KPI_Value = 
var _a=CALCULATE(SUM('Table'[KPI_Value]),FILTER('Table',[Year]=EARLIER('Table 3'[Table_Year]) && [Quarter]=EARLIER('Table 3'[Table_Quarter]) && [Site_ID]=EARLIER('Table 3'[Table_Site_ID]) && [KPI_ID]="KPI1"))
var _b=CALCULATE(SUM('Table'[KPI_Value]),FILTER('Table',[Year]=EARLIER('Table 3'[Table_Year]) && [Quarter]=EARLIER('Table 3'[Table_Quarter]) && [Site_ID]=EARLIER('Table 3'[Table_Site_ID]) && [KPI_ID]="KPI2"))
RETURN DIVIDE(_b,_a)

Then we can create a table.

Table 4 = 
var _a=SELECTCOLUMNS('Table 2',[Table_Year],[Table_Quarter],[Table_Site_ID],[Table_KPI_Name],[Table_KPI_Value])
var _b=SELECTCOLUMNS('Table 3',[Table_Year],[Table_Quarter],[Table_Site_ID],"Table_KPI_Name",[Value],[Table_KPI_Value])
RETURN UNION(_a,_b)

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[abanar2]

Hi @v-tangjie-msft,

Thank you for your ideas.

In my real case I have about 50 KPI Groups and this number can change, so maybe in the future I will have less or more. I can`t hardcode anyting into my script because then it would be very painful to maintane. 

[v-tangjie-msft]

Hi @abanar2 ,

If you want to avoid hard-coding, you can modify the DAX expression like this:

Table 2 = FILTER(SELECTCOLUMNS('Table',[Year],[Quarter],[Site_ID],[KPI_Name],[KPI_Value]),[KPI_Name] IN SELECTCOLUMNS(FILTER('Table',[Is_Total_KPI]="Yes"),[KPI_Name]))

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[abanar2]

This is the sample dataset.

Year	Quarter	Site_ID	KPI_ID	KPI_Name	Is_Total_KPI	KPI_Group	KPI_Value	Dividend_ID	Divisor_ID
2021	1	Site1	KPI1	Training Planned	Yes	% Training Done during the Quarter	79	KPI2	KPI1
2021	2	Site1	KPI1	Training Planned	Yes	% Training Done during the Quarter	75	KPI2	KPI1
2021	3	Site1	KPI1	Training Planned	Yes	% Training Done during the Quarter	67	KPI2	KPI1
2021	4	Site1	KPI1	Training Planned	Yes	% Training Done during the Quarter	77	KPI2	KPI1
2022	1	Site1	KPI1	Training Planned	Yes	% Training Done during the Quarter	33	KPI2	KPI1
2022	2	Site1	KPI1	Training Planned	Yes	% Training Done during the Quarter	71	KPI2	KPI1
2022	3	Site1	KPI1	Training Planned	Yes	% Training Done during the Quarter	11	KPI2	KPI1
2022	4	Site1	KPI1	Training Planned	Yes	% Training Done during the Quarter	49	KPI2	KPI1
2021	1	Site2	KPI1	Training Planned	Yes	% Training Done during the Quarter	34	KPI2	KPI1
2021	2	Site2	KPI1	Training Planned	Yes	% Training Done during the Quarter	25	KPI2	KPI1
2021	3	Site2	KPI1	Training Planned	Yes	% Training Done during the Quarter	73	KPI2	KPI1
2021	4	Site2	KPI1	Training Planned	Yes	% Training Done during the Quarter	10	KPI2	KPI1
2022	1	Site2	KPI1	Training Planned	Yes	% Training Done during the Quarter	67	KPI2	KPI1
2022	2	Site2	KPI1	Training Planned	Yes	% Training Done during the Quarter	96	KPI2	KPI1
2022	3	Site2	KPI1	Training Planned	Yes	% Training Done during the Quarter	29	KPI2	KPI1
2022	4	Site2	KPI1	Training Planned	Yes	% Training Done during the Quarter	83	KPI2	KPI1
2021	1	Site1	KPI2	Training Done	No	% Training Done during the Quarter	13	KPI2	KPI1
2021	2	Site1	KPI2	Training Done	No	% Training Done during the Quarter	16	KPI2	KPI1
2021	3	Site1	KPI2	Training Done	No	% Training Done during the Quarter	18	KPI2	KPI1
2021	4	Site1	KPI2	Training Done	No	% Training Done during the Quarter	28	KPI2	KPI1
2022	1	Site1	KPI2	Training Done	No	% Training Done during the Quarter	36	KPI2	KPI1
2022	2	Site1	KPI2	Training Done	No	% Training Done during the Quarter	88	KPI2	KPI1
2022	3	Site1	KPI2	Training Done	No	% Training Done during the Quarter	35	KPI2	KPI1
2022	4	Site1	KPI2	Training Done	No	% Training Done during the Quarter	88	KPI2	KPI1
2021	1	Site2	KPI2	Training Done	No	% Training Done during the Quarter	80	KPI2	KPI1
2021	2	Site2	KPI2	Training Done	No	% Training Done during the Quarter	34	KPI2	KPI1
2021	3	Site2	KPI2	Training Done	No	% Training Done during the Quarter	75	KPI2	KPI1
2021	4	Site2	KPI2	Training Done	No	% Training Done during the Quarter	18	KPI2	KPI1
2022	1	Site2	KPI2	Training Done	No	% Training Done during the Quarter	49	KPI2	KPI1
2022	2	Site2	KPI2	Training Done	No	% Training Done during the Quarter	33	KPI2	KPI1
2022	3	Site2	KPI2	Training Done	No	% Training Done during the Quarter	96	KPI2	KPI1
2022	4	Site2	KPI2	Training Done	No	% Training Done during the Quarter	27	KPI2	KPI1
2021	1	Site1	KPI3	Number of Focus Rooms in the Office	Yes	Number of Focus Rooms in the Office	100	0	0
2021	2	Site1	KPI3	Number of Focus Rooms in the Office	Yes	Number of Focus Rooms in the Office	100	0	0
2021	3	Site1	KPI3	Number of Focus Rooms in the Office	Yes	Number of Focus Rooms in the Office	100	0	0
2021	4	Site1	KPI3	Number of Focus Rooms in the Office	Yes	Number of Focus Rooms in the Office	100	0	0
2022	1	Site1	KPI3	Number of Focus Rooms in the Office	Yes	Number of Focus Rooms in the Office	90	0	0
2022	2	Site1	KPI3	Number of Focus Rooms in the Office	Yes	Number of Focus Rooms in the Office	90	0	0
2022	3	Site1	KPI3	Number of Focus Rooms in the Office	Yes	Number of Focus Rooms in the Office	90	0	0
2022	4	Site1	KPI3	Number of Focus Rooms in the Office	Yes	Number of Focus Rooms in the Office	110	0	0
2021	1	Site2	KPI3	Number of Focus Rooms in the Office	Yes	Number of Focus Rooms in the Office	70	0	0
2021	2	Site2	KPI3	Number of Focus Rooms in the Office	Yes	Number of Focus Rooms in the Office	70	0	0
2021	3	Site2	KPI3	Number of Focus Rooms in the Office	Yes	Number of Focus Rooms in the Office	70	0	0
2021	4	Site2	KPI3	Number of Focus Rooms in the Office	Yes	Number of Focus Rooms in the Office	70	0	0
2022	1	Site2	KPI3	Number of Focus Rooms in the Office	Yes	Number of Focus Rooms in the Office	70	0	0
2022	2	Site2	KPI3	Number of Focus Rooms in the Office	Yes	Number of Focus Rooms in the Office	70	0	0
2022	3	Site2	KPI3	Number of Focus Rooms in the Office	Yes	Number of Focus Rooms in the Office	70	0	0
2022	4	Site2	KPI3	Number of Focus Rooms in the Office	Yes	Number of Focus Rooms in the Office	72	0	0