Forum Discussion

Harrisfil's avatar
Harrisfil
Icon for Helper I rankHelper I
1 year ago
Solved

Matrix table with Target and YtD in the beginning

Hi, I have the following issues. I have a matrix table where i have categories in Rows and MonthName in Columns. In values i have multiple dax measures. in the format section i have toggled to th...
"Need Help"
  • MFelix's avatar
    MFelix
    1 year ago

    Hi Harrisfil ,

     

    Create a new field parameter table where you add all the measures, then add a measure with the following code:

     

    Measure = SWITCH(TRUE(),
                SELECTEDVALUE(Parameter[Parameter Order]) = 0 && SELECTEDVALUE(Categories[Categories]) = "Chat", [_AGENT_CES%],
                SELECTEDVALUE(Parameter[Parameter Order]) = 1 && SELECTEDVALUE(Categories[Categories]) = "Chat", [_AGENT_CSAT%],
                SELECTEDVALUE(Parameter[Parameter Order]) = 2 && SELECTEDVALUE(Categories[Categories]) = "Email", [_Mail_CES%],
                SELECTEDVALUE(Parameter[Parameter Order]) = 3 && SELECTEDVALUE(Categories[Categories]) = "Email", [_Mail_CSAT%],
                SELECTEDVALUE(Parameter[Parameter Order]) = 4 && SELECTEDVALUE(Categories[Categories]) = "Phone", [_PhoneCES%],
                SELECTEDVALUE(Parameter[Parameter Order]) = 5 && SELECTEDVALUE(Categories[Categories]) = "Phone", [_PhoneCSAT%]
                 )

    The order numbers must match the ones in the Field parameter table:

    Add a condittional formatting to your measure so everything match your formats:

    SWITCH(TRUE(),
                SELECTEDVALUE(Parameter[Parameter Order]) = 0 && SELECTEDVALUE(Categories[Categories]) = "Chat", "0.00%",
                SELECTEDVALUE(Parameter[Parameter Order]) = 1 && SELECTEDVALUE(Categories[Categories]) = "Chat", "0.00%",
                SELECTEDVALUE(Parameter[Parameter Order]) = 2 && SELECTEDVALUE(Categories[Categories]) = "Email", "0.00%",
                SELECTEDVALUE(Parameter[Parameter Order]) = 3 && SELECTEDVALUE(Categories[Categories]) = "Email", "0.00%",
                SELECTEDVALUE(Parameter[Parameter Order]) = 4 && SELECTEDVALUE(Categories[Categories]) = "Phone", "0.00%",
                SELECTEDVALUE(Parameter[Parameter Order]) = 5 && SELECTEDVALUE(Categories[Categories]) = "Phone", "0.00%"
                 )

     

    Now on the table visual add the parameter field on the rows and the new measure on values, also don't forget to check the option of the Show selected field:

     

     

MFelix's avatar
MFelix
Icon for Super User rankSuper User
1 year ago

Hi Harrisfil ,

 

Create a new field parameter table where you add all the measures, then add a measure with the following code:

 

Measure = SWITCH(TRUE(),
                SELECTEDVALUE(Parameter[Parameter Order]) = 0 && SELECTEDVALUE(Categories[Categories]) = "Chat", [_AGENT_CES%],
                SELECTEDVALUE(Parameter[Parameter Order]) = 1 && SELECTEDVALUE(Categories[Categories]) = "Chat", [_AGENT_CSAT%],
                SELECTEDVALUE(Parameter[Parameter Order]) = 2 && SELECTEDVALUE(Categories[Categories]) = "Email", [_Mail_CES%],
                SELECTEDVALUE(Parameter[Parameter Order]) = 3 && SELECTEDVALUE(Categories[Categories]) = "Email", [_Mail_CSAT%],
                SELECTEDVALUE(Parameter[Parameter Order]) = 4 && SELECTEDVALUE(Categories[Categories]) = "Phone", [_PhoneCES%],
                SELECTEDVALUE(Parameter[Parameter Order]) = 5 && SELECTEDVALUE(Categories[Categories]) = "Phone", [_PhoneCSAT%]
                 )

The order numbers must match the ones in the Field parameter table:

Add a condittional formatting to your measure so everything match your formats:

SWITCH(TRUE(),
                SELECTEDVALUE(Parameter[Parameter Order]) = 0 && SELECTEDVALUE(Categories[Categories]) = "Chat", "0.00%",
                SELECTEDVALUE(Parameter[Parameter Order]) = 1 && SELECTEDVALUE(Categories[Categories]) = "Chat", "0.00%",
                SELECTEDVALUE(Parameter[Parameter Order]) = 2 && SELECTEDVALUE(Categories[Categories]) = "Email", "0.00%",
                SELECTEDVALUE(Parameter[Parameter Order]) = 3 && SELECTEDVALUE(Categories[Categories]) = "Email", "0.00%",
                SELECTEDVALUE(Parameter[Parameter Order]) = 4 && SELECTEDVALUE(Categories[Categories]) = "Phone", "0.00%",
                SELECTEDVALUE(Parameter[Parameter Order]) = 5 && SELECTEDVALUE(Categories[Categories]) = "Phone", "0.00%"
                 )

 

Now on the table visual add the parameter field on the rows and the new measure on values, also don't forget to check the option of the Show selected field:

 

 

  • Harrisfil's avatar
    Harrisfil
    Icon for Helper I rankHelper I
    1 year ago

    Hello again,

     

    Are we able to create a conditional color formatting as well?

    each measure should have different target.

     

    Thank you

    • MFelix's avatar
      MFelix
      Icon for Super User rankSuper User
      1 year ago

      Hi Harrisfil ,

      You can if you add more details to the calculations:

      Measure = SWITCH(TRUE(),
                SELECTEDVALUE(Parameter[Parameter Order]) = 0 && SELECTEDVALUE(Categories[Categories]) = "Chat", 
SWITCH (TRUE(), [_AGENT_CES%] < 0.1, "Red", [_AGENT_CES%] < 0.5, "Yelllow", "Green")

,
   ....
SELECTEDVALUE(Categories[Categories]) = "Phone",, 
SWITCH (TRUE(), [_PhoneCSAT%] < 0.1, "Red", [_PhoneCSAT%] < 0.5, "Yelllow", "Green")
 
                 )

      This should give the expected result.

       

      • Harrisfil's avatar
        Harrisfil
        Icon for Helper I rankHelper I
        1 year ago

        thank you for the guidance 

        i did something like that at last: 

         

        Measure_Formatting =
        SWITCH(TRUE(),
            SELECTEDVALUE(Parameter[Parameter Order]) = 0,
                IF([CES% Total] < 0.9, 1, 3),
            SELECTEDVALUE(Parameter[Parameter Order]) = 1,
                IF([CSAT% Total] < 0.85, 1, 3),
            SELECTEDVALUE(Parameter[Parameter Order]) = 2,

        ....

        ))
        and i added it in conditional formmating  under cell elements and set 1 as red otterwise green and it worked.
         
        your help is highly appreciated