Forum Discussion

tmhrzgr's avatar
tmhrzgr
Frequent Visitor
6 years ago
Solved

Measure with data from other table with multiple conditions

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:...
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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's avatar
tmhrzgr
Frequent Visitor
6 years ago

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's avatar
v-lid-msft
Community Support
6 years ago

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,