Combining values from 2 tables to display only certain values

[Belindah]

I have 2 tables that are calculating SLA totals based on domiciled and non-domiciled locations. The calculations work perfectly. The problem I am having is that I need to show the values by team in one visual depending on if the team is domiciled or non-domiciled. I created a table with the following formula: 

TeamSLAType =

DATATABLE(

    "Team", STRING,

    "SLA Type", STRING,

    {

        {"Team A", "Domiciled"},

        {"Team B", "Domiciled"},

        {"Team C", "Domiciled"},

        {"Team D", "Non-Domiciled"},

        {"Team E", "Non-Domiciled"},

        {"Team F", "Non-Domiciled"},

    }

)

Then I created this measure:

Selected SLA Percentage =

VAR SLAType = SELECTEDVALUE(TeamSLAType[SLA Type])

RETURN

    SWITCH(

        TRUE(),

        SLAType = "Domiciled", [Combined SLA Met Percentage by Team Domiciled],

        SLAType = "Non-Domiciled", [Combined SLA Met Percentage by Team Non-Domiciled],

        BLANK() // Handle cases where SLA Type is not matched

    )

The problem I am having is the measure is showing the average of total SLA percentage instead of the individual values. The relationships are set up, and the values are set to no calculation. I can't figure out how to make this work. Specifically, I am trying to create a matrix visual with the team names as the columns and the SLA as the value. Even when I try it in a different visual, it is showing averages.

[Anonymous]

Hi @Belindah ,

I'm sorry I can't understand your needs, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Neeko Tang

[Belindah]

Here is a link to the test file I created. I created 3 matrix visuals. The first one shows domiciled SLA percentages per team, the second shows non-domiciled SLA percentages per team, and the last one is the TeamSLAType percentages. That is the one I am having difficulty with. It is showing an average instead of extracting the individual numbers from the other 2 tables (CombinedTicketsDomiciled and CombinedTicketsNonDomiciled). Test SLA.pbix

[Anonymous · Accepted solution]

Hi @Belindah ,

We can create two measures.

Measure = 
VAR SLAType = SELECTEDVALUE(TeamSLAType[SLA Type])
var _table1=SUMMARIZE(ALLSELECTED('CombinedTicketsDomiciled'),[Team],"value1",[Combined SLA Met Percentage by Team Domiciled])
var _table2=SUMMARIZE(ALLSELECTED('CombinedTicketsNonDomiciled'),[Team],"value2",[Combined SLA Met Percentage by Team Non-Domiciled])

RETURN
    SWITCH(
        TRUE(),
        SLAType = "Domiciled", MAXX(FILTER(_table1,[Team] in VALUES('TeamSLAType'[Team])),[value1]),
        SLAType = "Non-Domiciled", MAXX(FILTER(_table2,[Team] in VALUES('TeamSLAType'[Team])),[value2]),
        BLANK() // Handle cases where SLA Type is not matched
    )

Measure 2 = SUMX(VALUES('TeamSLAType'[Team]),[Measure])

Best Regards,

Neeko Tang

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

[Belindah]

That worked. Thank you so much!

[parry2k]

@Belindah what is the expression of following measures:

[Combined SLA Met Percentage by Team Domiciled]

[Combined SLA Met Percentage by Team Non-Domiciled]

[Belindah]

Combined SLA Met Percentage by Team Domiciled =

CALCULATE(

    DIVIDE(

        SUMX(CombinedTicketsDomiciled, CombinedTicketsDomiciled[SLA Met]),

        COUNTROWS(CombinedTicketsDomiciled),

        0

    ) * 100,

    ALLEXCEPT(CombinedTicketsDomiciled, CombinedTicketsDomiciled[Team])

)

and

Combined SLA Met Percentage by Team Non-Domiciled =

CALCULATE(

    DIVIDE(

        SUMX(CombinedTicketsNonDomiciled, CombinedTicketsNonDomiciled[SLA Met]),

        COUNTROWS(CombinedTicketsNonDomiciled),

        0

    ) * 100,

    ALLEXCEPT(CombinedTicketsNonDomiciled, CombinedTicketsNonDomiciled[Team])

)

Those measures work correctly, but I can't get the other table to pull the individual values.