Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

DAX Measure help

Hi!   I am trying to migrate a calculation from Qlik to Power BI. But I am having difficulties to make it work 100%.  Would love to have some help here regarding this..   So what I want to do in...
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi,

     

    Thanks for your response!

     

    There are some things in this calculation that are not correct from what I asked about from the start.

    - Should be divided by No of Orders in the end

    - Should exclude all rows that has Workgroup = *GDPR* 

     

    I did however solve it afterwards it seems!

     

    VAR dCall =
    CALCULATE(
        [No of Calls],
            Filter( dim_wrapupgroup, NOT( dim_wrapupgroup[Wrap Up Group] IN {"BO Action",
                                                                            "BO Support",
                                                                            "STORE",
                                                                            "H&M CLUB"})),
            Filter( view_interaction, SEARCH("*GDPR*",view_interaction[Workgroup],1,0) = 0 )
    )

    VAR dEmail =
    CALCULATE(
        [No of Emails],
            Filter( dim_wrapupgroup, NOT( dim_wrapupgroup[Wrap Up Group] IN {"BO Action",
                                                                            "BO Support",
                                                                            "STORE",
                                                                            "H&M CLUB"})),
            Filter( view_interaction, SEARCH("*GDPR*",view_interaction[Workgroup],1,0) = 0 )
    )

    VAR dChat =
    SWITCH(TRUE(),
    MAX(dim_agenttype[Agent Type]) = "Live Agent", [No of Chats Answered LA],
    MAX(dim_agenttype[Agent Type]) = "VA Agent", [No of Chats Answered VA],
    [No of Chats Answered]
    )

    VAR dOrder =
    [No of Orders]

    RETURN
    IFERROR((dCall+dEmail+dChat) / dOrder,
        BLANK()
    )
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi  Anonymous ,

I created some data:

 

Here are the steps you can follow：

1. Create measure.

Contact per Order % =
var _sum1=
SUMX(FILTER(ALL(dim_wrapupgroup),
NOT('dim_wrapupgroup'[Wrap Up Group]) in {"BO Action", "BO Support"}
&&
CONTAINSSTRING('dim_wrapupgroup'[Workgroup],"GDPR")=TRUE())
,[No of Calls]+[No of Emails])
var _sumif=
IF(
    MAX('dim_wrapupgroup'[Agent Type]) = "Live Agent",
        MAX('dim_wrapupgroup'[No of Chats Answered LA]),
            IF(
                MAX('dim_wrapupgroup'[Agent Type]) = "VA Agent",
                    MAX('dim_wrapupgroup'[No of Chats Answered VA]),
                        MAX('dim_wrapupgroup'[No of Chats Answered])
            ))
return
_sum1+_sumif

2. Result:

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi,

 

Thanks for your response!

 

There are some things in this calculation that are not correct from what I asked about from the start.

- Should be divided by No of Orders in the end

- Should exclude all rows that has Workgroup = *GDPR* 

 

I did however solve it afterwards it seems!

 

VAR dCall =
CALCULATE(
    [No of Calls],
        Filter( dim_wrapupgroup, NOT( dim_wrapupgroup[Wrap Up Group] IN {"BO Action",
                                                                        "BO Support",
                                                                        "STORE",
                                                                        "H&M CLUB"})),
        Filter( view_interaction, SEARCH("*GDPR*",view_interaction[Workgroup],1,0) = 0 )
)

VAR dEmail =
CALCULATE(
    [No of Emails],
        Filter( dim_wrapupgroup, NOT( dim_wrapupgroup[Wrap Up Group] IN {"BO Action",
                                                                        "BO Support",
                                                                        "STORE",
                                                                        "H&M CLUB"})),
        Filter( view_interaction, SEARCH("*GDPR*",view_interaction[Workgroup],1,0) = 0 )
)

VAR dChat =
SWITCH(TRUE(),
MAX(dim_agenttype[Agent Type]) = "Live Agent", [No of Chats Answered LA],
MAX(dim_agenttype[Agent Type]) = "VA Agent", [No of Chats Answered VA],
[No of Chats Answered]
)

VAR dOrder =
[No of Orders]

RETURN
IFERROR((dCall+dEmail+dChat) / dOrder,
    BLANK()
)