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 ,

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