Forum Discussion

Belindah's avatar
Belindah
Frequent Visitor
2 years ago
Solved

Combining values from 2 tables to display only certain values

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.
 
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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. 

6 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    2 years ago

    Belindah what is the expression of following measures:

     

    [Combined SLA Met Percentage by Team Domiciled]

    [Combined SLA Met Percentage by Team Non-Domiciled]

    • Belindah's avatar
      Belindah
      Frequent Visitor
      2 years ago
      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.
    • Belindah's avatar
      Belindah
      Frequent Visitor
      1 year ago

      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's avatar
        Anonymous
        Not applicable
        1 year ago

        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.