Calculate KPI Average value and Count number of times KPI exceeded Threshold

FMF — Fri, 28 Feb 2020 13:13:04 GMT

I want to achieve the below with DAX commands

1) Avg Value: Display the Average value of KPI IDx (x = 1, 2 ... N) from Table 1 based on a KPI selected @ run time. The User selected KPI @ run time will be based on KPI Name field from Table 3 and it has to be selected from a slicer. It has be a dynamic dax calculation, considering the fact that there are huge number of KPI columns and the list in Table 3 can grow

2) Count Value: Count the number of times KPI IDx (x = 1, 2 ... N) from Table 1 is above or below a user defined threshold. The user defined threshold will be based on Threshold field from Table 3 and the above or below condition will be based on the Sign field from Table 3

Table 1 & Table 2 are linked in the Model based on Joiner Field. the Joiner field is uinque in Table 1

Table 1

Joiner Field	Attribute 1	Attribute 2	….	Attribute n
A
B
C
D

Table 2

Joiner Field	Date	KPI ID 1	KPI ID 2	….	KPI ID n
A	01.01.2020	98	0.3
A	02.01.2020	95	0.7
A	03.01.2020	93	0.8
B	01.01.2020	92	0.2
B	02.01.2020	92	0.1
B	03.01.2020	91	0.1
C	01.01.2020	98	0.9
C	02.01.2020	99	1.5
C	03.01.2020	98	1.2
..
..

Table 3

KPI ID	KPI Name	Threshold	Sign
KPI 1	xxxxx	95	<
KPI 2	yyyyy	0.5	>
...
KPI n	zzzzz	98	<

Ouput Examples

User Selected KPI Name = xxxx

Joiner Field (Table 1)	Attribute 1	Attribute 2	….	Attribute n	KPI (Avg Value)	KPI (Count)
A					95.33	2
B					91.66	3
C					98.33	0
D

User Selected KPI Name = yyyy

Joiner Field (Table 1)	Attribute 1	Attribute 2	….	Attribute n	KPI (Avg Value)	KPI (Count)
A					0.6	2
B					0.13	0
C					1.2	3
D

Floyd

Re: Calculate KPI Average value and Count number of times KPI exceeded Threshold

kentyler — Sun, 01 Mar 2020 17:20:04 GMT

I'm not certain how your KPI's are calculated. I am pretty sure you will have to unpivot your data, like this Joiner FieldDateAttributeValue

A	1/1/2020	KPI ID 1	98
A	1/1/2020	KPI ID 2	0.3
A	2/1/2020	KPI ID 1	95
A	2/1/2020	KPI ID 2	0.7
A	3/1/2020	KPI ID 1	93
A	3/1/2020	KPI ID 2	0.8
B	1/1/2020	KPI ID 1	92
B	1/1/2020	KPI ID 2	0.2
B	2/1/2020	KPI ID 1	92
B	2/1/2020	KPI ID 2	0.1
B	3/1/2020	KPI ID 1	91
B	3/1/2020	KPI ID 2	0.1
C	1/1/2020	KPI ID 1	98
C	1/1/2020	KPI ID 2	0.9
C	2/1/2020	KPI ID 1	99
C	2/1/2020	KPI ID 2	1.5
C	3/1/2020	KPI ID 1	98
C	3/1/2020	KPI ID 2	1.2

to avoid overly complex dax calculations

topic Calculate KPI Average value and Count number of times KPI exceeded Threshold in DAX Commands and Tips

Calculate KPI Average value and Count number of times KPI exceeded Threshold

Re: Calculate KPI Average value and Count number of times KPI exceeded Threshold