Need help with my measure

Question

Hello Community,&nbsp;&nbsp;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 NameMeaningYearYear of the data rowQuarterQuarter of the data rowSite_IDSite_ID of the data rowKPI_IDKPI_ID of the data rowKPI_NameKPI_Name of the data rowIs_Total_KPIYes: the selected KPI used as itself as KPI GroupKPI_GroupGroup of the KPIsKPI_ValueThe value for the selected year, quarter, site and KPIDividend_IDThe ID of the KPI what we use as dividend for in the KPI Group calculationDivisor_IDThe ID of the KPI what we use as divisor for in the KPI Group calculation&nbsp;I need to create a measure that can handle three different calculation scenarios:When we calculate a division between KPI pairs (e.g., KPI2 / KPI1)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)When our KPI isn’t a member of any calculation but we evaluate it as itself (e.g., KPI3)&nbsp;Ultimately, I want to visualize the&nbsp;KPI_Group_Values&nbsp;with the&nbsp;Year,&nbsp;Quarter,&nbsp;Site_ID, and&nbsp;KPI_Group_Name&nbsp;in a table or matrix.&nbsp;I will paste a data sample into the comments.&nbsp;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:&nbsp;Year Quarter Site_ID KPI_Group KPI_Group_Value20211Site1Training Planned7920212Site1Training Planned7520213Site1Training Planned6720214Site1Training Planned7720211Site1% Training Done during the Quarter16.46%20212Site1% Training Done during the Quarter21.33%20213Site1% Training Done during the Quarter26.87%20214Site1% Training Done during the Quarter36.36%20211Site1Number of Focus Rooms in the Office10020212Site1Number of Focus Rooms in the Office10020213Site1Number of Focus Rooms in the Office10020214Site1Number of Focus Rooms in the Office10020211Site2Training Planned3420212Site2Training Planned2520213Site2Training Planned7320214Site2Training Planned1020211Site2% Training Done during the Quarter235.29%20212Site2% Training Done during the Quarter136.00%20213Site2% Training Done during the Quarter102.74%20214Site2% Training Done during the Quarter180.00%20211Site2Number of Focus Rooms in the Office7020212Site2Number of Focus Rooms in the Office7020213Site2Number of Focus Rooms in the Office7020214Site2Number of Focus Rooms in the Office7020221Site1Training Planned3320222Site1Training Planned7120223Site1Training Planned1120224Site1Training Planned4920221Site1% Training Done during the Quarter109.09%20222Site1% Training Done during the Quarter123.94%20223Site1% Training Done during the Quarter318.18%20224Site1% Training Done during the Quarter179.59%20221Site1Number of Focus Rooms in the Office9020222Site1Number of Focus Rooms in the Office9020223Site1Number of Focus Rooms in the Office9020224Site1Number of Focus Rooms in the Office11020221Site2Training Planned6720222Site2Training Planned9620223Site2Training Planned2920224Site2Training Planned8320221Site2% Training Done during the Quarter73.13%20222Site2% Training Done during the Quarter34.38%20223Site2% Training Done during the Quarter331.03%20224Site2% Training Done during the Quarter32.53%20221Site2Number of Focus Rooms in the Office7020222Site2Number of Focus Rooms in the Office7020223Site2Number of Focus Rooms in the Office7020224Site2Number of Focus Rooms in the Office72

abanar2 · Answer

This is the sample dataset.YearQuarterSite_IDKPI_IDKPI_NameIs_Total_KPIKPI_GroupKPI_ValueDividend_IDDivisor_ID20211Site1KPI1Training PlannedYes% Training Done during the Quarter79KPI2KPI120212Site1KPI1Training PlannedYes% Training Done during the Quarter75KPI2KPI120213Site1KPI1Training PlannedYes% Training Done during the Quarter67KPI2KPI120214Site1KPI1Training PlannedYes% Training Done during the Quarter77KPI2KPI120221Site1KPI1Training PlannedYes% Training Done during the Quarter33KPI2KPI120222Site1KPI1Training PlannedYes% Training Done during the Quarter71KPI2KPI120223Site1KPI1Training PlannedYes% Training Done during the Quarter11KPI2KPI120224Site1KPI1Training PlannedYes% Training Done during the Quarter49KPI2KPI120211Site2KPI1Training PlannedYes% Training Done during the Quarter34KPI2KPI120212Site2KPI1Training PlannedYes% Training Done during the Quarter25KPI2KPI120213Site2KPI1Training PlannedYes% Training Done during the Quarter73KPI2KPI120214Site2KPI1Training PlannedYes% Training Done during the Quarter10KPI2KPI120221Site2KPI1Training PlannedYes% Training Done during the Quarter67KPI2KPI120222Site2KPI1Training PlannedYes% Training Done during the Quarter96KPI2KPI120223Site2KPI1Training PlannedYes% Training Done during the Quarter29KPI2KPI120224Site2KPI1Training PlannedYes% Training Done during the Quarter83KPI2KPI120211Site1KPI2Training DoneNo% Training Done during the Quarter13KPI2KPI120212Site1KPI2Training DoneNo% Training Done during the Quarter16KPI2KPI120213Site1KPI2Training DoneNo% Training Done during the Quarter18KPI2KPI120214Site1KPI2Training DoneNo% Training Done during the Quarter28KPI2KPI120221Site1KPI2Training DoneNo% Training Done during the Quarter36KPI2KPI120222Site1KPI2Training DoneNo% Training Done during the Quarter88KPI2KPI120223Site1KPI2Training DoneNo% Training Done during the Quarter35KPI2KPI120224Site1KPI2Training DoneNo% Training Done during the Quarter88KPI2KPI120211Site2KPI2Training DoneNo% Training Done during the Quarter80KPI2KPI120212Site2KPI2Training DoneNo% Training Done during the Quarter34KPI2KPI120213Site2KPI2Training DoneNo% Training Done during the Quarter75KPI2KPI120214Site2KPI2Training DoneNo% Training Done during the Quarter18KPI2KPI120221Site2KPI2Training DoneNo% Training Done during the Quarter49KPI2KPI120222Site2KPI2Training DoneNo% Training Done during the Quarter33KPI2KPI120223Site2KPI2Training DoneNo% Training Done during the Quarter96KPI2KPI120224Site2KPI2Training DoneNo% Training Done during the Quarter27KPI2KPI120211Site1KPI3Number of Focus Rooms in the OfficeYesNumber of Focus Rooms in the Office1000020212Site1KPI3Number of Focus Rooms in the OfficeYesNumber of Focus Rooms in the Office1000020213Site1KPI3Number of Focus Rooms in the OfficeYesNumber of Focus Rooms in the Office1000020214Site1KPI3Number of Focus Rooms in the OfficeYesNumber of Focus Rooms in the Office1000020221Site1KPI3Number of Focus Rooms in the OfficeYesNumber of Focus Rooms in the Office900020222Site1KPI3Number of Focus Rooms in the OfficeYesNumber of Focus Rooms in the Office900020223Site1KPI3Number of Focus Rooms in the OfficeYesNumber of Focus Rooms in the Office900020224Site1KPI3Number of Focus Rooms in the OfficeYesNumber of Focus Rooms in the Office1100020211Site2KPI3Number of Focus Rooms in the OfficeYesNumber of Focus Rooms in the Office700020212Site2KPI3Number of Focus Rooms in the OfficeYesNumber of Focus Rooms in the Office700020213Site2KPI3Number of Focus Rooms in the OfficeYesNumber of Focus Rooms in the Office700020214Site2KPI3Number of Focus Rooms in the OfficeYesNumber of Focus Rooms in the Office700020221Site2KPI3Number of Focus Rooms in the OfficeYesNumber of Focus Rooms in the Office700020222Site2KPI3Number of Focus Rooms in the OfficeYesNumber of Focus Rooms in the Office700020223Site2KPI3Number of Focus Rooms in the OfficeYesNumber of Focus Rooms in the Office700020224Site2KPI3Number of Focus Rooms in the OfficeYesNumber of Focus Rooms in the Office7200

anonymous · Answer

Hi&nbsp;abanar2&nbsp;,
&nbsp;
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%

&nbsp;
Best Regards,
Neeko Tang

abanar2 · Answer

20211Site1% Training Done during the Quarter16.46%=&nbsp;Dividend row:YearQuarterSite_IDKPI_IDKPI_NameIs_Total_KPIKPI_GroupKPI_ValueDividend_IDDivisor_ID20211Site1KPI1Training PlannedYes% Training Done during the Quarter79KPI2KPI1&nbsp;Divisor row:20211Site1KPI2Training DoneNo% Training Done during the Quarter13KPI2KPI1&nbsp;13 / 79 =&nbsp;0.164556962&nbsp;

anonymous · Answer

Hi&nbsp;abanar2&nbsp;,
&nbsp;
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]) &amp;&amp; [Quarter]=EARLIER('Table 3'[Table_Quarter]) &amp;&amp; [Site_ID]=EARLIER('Table 3'[Table_Site_ID]) &amp;&amp; [KPI_ID]="KPI1"))
var _b=CALCULATE(SUM('Table'[KPI_Value]),FILTER('Table',[Year]=EARLIER('Table 3'[Table_Year]) &amp;&amp; [Quarter]=EARLIER('Table 3'[Table_Quarter]) &amp;&amp; [Site_ID]=EARLIER('Table 3'[Table_Site_ID]) &amp;&amp; [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.&nbsp;

abanar2 · Answer

Hi&nbsp;Anonymous,&nbsp;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.&nbsp;

Forum Discussion

Need help with my measure

6 Replies

Related content

Measure

DAX Measure

Path Measure

Count Measure

% of total measure

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

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