Measures with KPI

[maserr]

Hi there,

I would like to compute a new measure based on the results of several KPIs. I would try to explain myself as best as I can:

I have some KPI's created in my tabular cube in SSAS:

KPI1   Value 3    Goal 5

KPI2   Value 9    Goal 10

KPI3   Value 11  Goal 10

KPI4   Value 2    Goal 5

As it can be seen, the KPI1 and KPI4 aren't achieving the goal, whereas the KPI2 and KPI3 are. I would need a new measure (or maybe a new KPI) where I could calculate this: [KPI1 (N) + KPI2 (Y) + KPI3 (Y) + KPI4 (N)]/ # KPI where Y/N would be a True/False if I amb achieving the goal for each KPI. In this basic example, of course, I have only 2 different goals but in fact, I would have a lot, so I would want to reuse the goal already set on the creation of the KPI and avoid creating the measure depending on the goal put manually (per example,  in case the goal is modified I would have to update in the KPI and in the measure).

In that case, I would have a measure with the following result: 2/4, my total achievement is 0.5.

Is there any way to create a measure in DAX where I could access directly to the value stored in the Goal of the KPI?

Hope it is clear,

Thanks in advance,

[v-lid-msft · Accepted solution]

Hi @maserr ,

Based on this document, A KPI (Key Performance Indicator), in a tabular model, defined by a Base measure, against a Target value, also defined by a measure or by an absolute value.

If the Target value is absolute value, we did not find a way to get it dynamically , but if it is stored in another table or get by measure. We can try to use following measure to meet your requirement:

Measure =
 (
    IF ( [KPI 1 Value] >= [KPI 1 Target], 1, 0 )
        + IF ( [KPI 2 Value] >= [KPI 2 Target], 1, 0 )
        + IF ( [KPI 3 Value] >= [KPI 3 Target], 1, 0 )
        + IF ( [KPI 4 Value] >= [KPI 4 Target], 1, 0 )
) / 4

If you have a target table such as following

TargetName	TargetValue
A	5
B	10
C	15
D	20

Then the target value measure can be following

KPI 1 Target = Calculate(sum('Target'[Target Value]), 'Target'[Target Name] = A)

KPI 2 Target = Calculate(sum('Target'[Target Value]), 'Target'[Target Name] = B)

By the way, PBIX file as attached.

Best regards,