Measure with data from other table with multiple conditions

[tmhrzgr]

Hi all,

I'm a little bit confused by a Measure as I'm looking for the right solution, but can't find it. 😞 Hope you can help me. (And sorry for the wall of text. :))

First of all some sample-data:

Table: Tickets

TicketID	OpenDateTime	Team	SlaTyp
1	16.06.2019 12:05:17	PK	Standard
2	16.06.2019 14:17:20	GK	Standard
3	17.07.2019 11:32:22	GK	Basic Plus
4	18.07.2019 17:00:21	GK	Standard

This is the "main"-table. Here is of course more data stored, but for that example this table is good.

Table: BZuLi

TicketID	AktionDateTime	Aktion	ToDoDate	SlaEinhaltung
1	16.06.2019 12:00:23	Incoming Mail
1	16.06.2019 12:05:17	Ticket created
1	16.06.2019 12:06:06	Note
1	16.06.2019 12:17:44	ToDo	17.06.2019	1
1	17.06.2019 14:12:44	Ticket closed
1	20.06.2019 08:52:21	Ticket reopened
1	20.06.2019 08:55:00	ToDo	21.06.2019	0
1	22.06.2019 13:22:11	Ticket closed
2	16.06.2019 13:48:31	Incoming Mail
2	16.06.2019 14:17:20	Ticket created
2	16.06.2019 14:20:19	ToDo	17.06.2019	1
2	17.06.2019 09:09:12	ToDo	20.06.2019	1
2	20.06.2019 15:42:31	Ticket closed

For every ticket in the table "Tickets" there can be multiple actions. Like here: for every ticket we have a incoming email, a ticket creation, notes, multiple todos, maybe a ticket reopening and so on. Please note here the column "SlaEinhaltung". This column is set for every action labeled with "ToDo" and checks if the NEXT action with "ToDo" or "Ticket closed" is at (or before) the date of the set "ToDoDate". If it is, the target is reached (1), if not, then not (0).

The table "BZuLi" have a relationship to the table "Tickets" via TicketID (1:n, 1 ticket can have multiple entrys in BZuLi).

Table: ZieleNV

Team	SlaTyp	70p	100p	150p
PK	Standard	87,00%	90,50%	93,00%
GK	Standard	89,00%	93,50%	96,50%
GK	Basic Plus	89,00%	93,50%	96,50%

This table is a definition-table for my goal attainment. For example: I'm working for the Team "GK" and working with Standard and Basic Plus-Tickets. If 93,50% of the BZuLi-Column "SlaEinhaltung" are having a positive value (1), I'm reaching 100% of my goal. When there more than 96,50%, I'm reaching 150% of my goal. With 94,57% it's in between.

The calculation for the percentage itself is easy. I have a Measure for that, that sums up the positive values in BZuLi (1) and the count of all values (0 and 1) and do some math:

SlaProzentBZuLi = 

VAR SlaPositiv = CALCULATE(COUNTROWS(BZuLi);BZuLi[SlaEinhaltung] = 1)
VAR SlaGesamt  = CALCULATE(COUNTROWS(BZuLi);NOT(ISBLANK(BZuLi[SlaEinhaltung])))

RETURN SlaPositiv / SlaGesamt

But now I'm challenged to get this value into the goal. My thinking here is, that I need a new measure that checks the value of the first Measure "SlaProzentBZuLi" and calculates them into that goal. But I need to check first the Team and the SlaTyp to get the right value for that goal. For that I did this Measure:

ZielProzentBZuLi = 

VAR z70p  = LOOKUPVALUE(ZieleNV[70p];ZieleNV[Team];LOOKUPVALUE(Tickets[Team];Tickets[TicketID];SELECTEDVALUE(BZuLi[TicketID]));ZieleNV[SlaTyp];LOOKUPVALUE(Tickets[SlaTyp];Tickets[TicketID];SELECTEDVALUE(BZuLi[TicketID])))
VAR z100p = LOOKUPVALUE(ZieleNV[100p];ZieleNV[Team];LOOKUPVALUE(Tickets[Team];Tickets[TicketID];SELECTEDVALUE(BZuLi[TicketID]));ZieleNV[SlaTyp];LOOKUPVALUE(Tickets[SlaTyp];Tickets[TicketID];SELECTEDVALUE(BZuLi[TicketID])))
VAR z150p = LOOKUPVALUE(ZieleNV[150p];ZieleNV[Team];LOOKUPVALUE(Tickets[Team];Tickets[TicketID];SELECTEDVALUE(BZuLi[TicketID]));ZieleNV[SlaTyp];LOOKUPVALUE(Tickets[SlaTyp];Tickets[TicketID];SELECTEDVALUE(BZuLi[TicketID])))


RETURN SWITCH(TRUE();
            BZuLi[SlaProzentBZuLi] >= z150p;1,5000;
            BZuLi[SlaProzentBZuLi] >= z100p && BZuLi[SlaProzentBZuLi] < z150p;1,0+0,5*(BZuLi[SlaProzentBZuLi]-z100p)/(z150p-z100p);
            BZuLi[SlaProzentBZuLi] >= z70p  && BZuLi[SlaProzentBZuLi] < z100p;0,7+0,3*(BZuLi[SlaProzentBZuLi]-z70p)/(z100p-z70p);
            BZuLi[SlaProzentBZuLi] < z70p;0,7*BZuLi[SlaProzentBZuLi])

But this is - sadly - not working. The result is always 150%, even when the monthly value is below the 70%-goal. I have a matrix for that (see left). Look at the values for "Juli 2019". The percentage of the target reached are calculated the right way. But the Measure "ZielProzentBZuLi" calculates it into the wrong value (150%).

How can I solve this?

Thank you for your patience and hopefully solution! 🙂

[v-lid-msft · Accepted solution]

Hi @tmhrzgr ,

We can try to use the following measure to solve it:

ZielProzentBZuLi =
VAR z70p =
    CALCULATE (
        MAX ( ZieleNV[70p] );
        FILTER (
            ZieleNV;
            ZieleNV[Team] IN DISTINCT ( Tickets[Team] )
                && ZieleNV[SlaTyp] IN DISTINCT ( Tickets[SlaTyp] )
        )
    )
VAR z100p =
    CALCULATE (
        MAX ( ZieleNV[100p] );
        FILTER (
            ZieleNV;
            ZieleNV[Team] IN DISTINCT ( Tickets[Team] )
                && ZieleNV[SlaTyp] IN DISTINCT ( Tickets[SlaTyp] )
        )
    )
VAR z150p =
    CALCULATE (
        MAX ( ZieleNV[150p] );
        FILTER (
            ZieleNV;
            ZieleNV[Team] IN DISTINCT ( Tickets[Team] )
                && ZieleNV[SlaTyp] IN DISTINCT ( Tickets[SlaTyp] )
        )
    )
RETURN
    SWITCH (
        TRUE ();
        BZuLi[SlaProzentBZuLi] >= z150p; 1,5000;
        BZuLi[SlaProzentBZuLi] >= z100p
            && BZuLi[SlaProzentBZuLi] < z150p; 1,0 + 0,5 * ( BZuLi[SlaProzentBZuLi] - z100p ) / ( z150p - z100p );
        BZuLi[SlaProzentBZuLi] >= z70p
            && BZuLi[SlaProzentBZuLi] < z100p; 0,7 + 0,3 * ( BZuLi[SlaProzentBZuLi] - z70p ) / ( z100p - z70p );
        BZuLi[SlaProzentBZuLi] < z70p; 0,7 * BZuLi[SlaProzentBZuLi]
    )

If it does not work, could you please check the result of variable z70p/z100p/z150p in origin measure and new measure? What field is filtered in the report?

Best regards,

[tmhrzgr]

Hi @v-lid-msft,

thanks for your reply. Your solution works as I expect - awesome! Thank you for that! 🙂

Just one quick follow-up to this (even if it's not related to the topic now): do I have some incluence into the calculation of the "Sum"-column in a matrix-visual?

Let's asume the following example in the matrix-visual:

In Mai 2019 we have a overall-value of 85,70%. This is because the count of "Standard"-tickets is higher than "Basic Plus"-tickets, so the weight of these tickets is higher, too, wich results to this overall-value.

What do I have to do, when I want "Standard" and "Basic Plus" to have the same weight? So: (92,47%+76,48%)/2 = 84,48%? Is this achievable within the same measure or do I need a second one?

Thank you for your kind support! I appreciate it alreday! 🙂

[v-lid-msft]

Hi @tmhrzgr ,

We can try to use the following measure to meet your requirement:

Measure =
CALCULATE (
    AVERAGEX ( DISTINCT ( 'Tickets'[SlaTyp] ), CALCULATE ( [Old Measure] ) ),
    ALLSELECTED ( 'Tickets'[SlaTyp] )
)

If it does not work, could you pleas share the formula of Measure "Target NV" after remove any confidential information?

Best regards,