Forum Discussion

jignaski18's avatar
jignaski18
Helper II
6 years ago
Solved

Data Manipulation for Matrix Table

I need some help with formatting my data in order to organize my matrix table correctly. I have searched the web for 2-3 days now trying multiple methods but with no luck on a solution. I have tried ...
Need Help
Tutorial Requests
  • MFelix's avatar
    MFelix
    6 years ago

    Hi jignaski18 ,

     

    You also need to add the selection of the measure as in the previous formula should be:

    Conditional Formatting=
               SWITCH(TRUE();
                  SELECTEDVALUE(Table[MeasureName]) = 1 && [Measure1] >0 ; "#FFFFFF";
                  SELECTEDVALUE(Table[MeasureName]) = 1 && [Measure1] <0 ; "#000000";
                   ...;...;
                  SELECTEDVALUE(Table[MeasureName]) = 99 && [Measure99] > 0 ; "#123456";
                  SELECTEDVALUE(Table[MeasureName]) = 99 && [Measure99] < 0 ; "#999999"
                 )

     

    Should work as expected, sorry for misleading you.

MFelix's avatar
MFelix
Super User
6 years ago

Hi jignaski18 ,

 

You need to follow the same logic but make a switch stamente with more arguments for example:

 

Conditional Formatting=
               SWITCH(TRUE();
                   [Measure1] >0 ; "#FFFFFF";
                   [Measure1] <0 ; "#000000";
                   ...;...;
                   [Measure99] > 0 ; "#123456";
                   [Measure99] < 0 ; "#999999"
                 )

 

the use this as color if you want to use trafic ligth similars you just need to replace the "#FFFF" by numbers example (1, 2, 3) and then set each one for the sign you need.

 

 

 

jignaski18's avatar
jignaski18
Helper II
6 years ago

Unless I am doing something wrong, this switch just takes the first true statement and applies it to the whole table since there is only "one" value. 

 

  • MFelix's avatar
    MFelix
    Super User
    6 years ago

    Hi jignaski18 ,

     

    You also need to add the selection of the measure as in the previous formula should be:

    Conditional Formatting=
               SWITCH(TRUE();
                  SELECTEDVALUE(Table[MeasureName]) = 1 && [Measure1] >0 ; "#FFFFFF";
                  SELECTEDVALUE(Table[MeasureName]) = 1 && [Measure1] <0 ; "#000000";
                   ...;...;
                  SELECTEDVALUE(Table[MeasureName]) = 99 && [Measure99] > 0 ; "#123456";
                  SELECTEDVALUE(Table[MeasureName]) = 99 && [Measure99] < 0 ; "#999999"
                 )

     

    Should work as expected, sorry for misleading you.

    • jignaski18's avatar
      jignaski18
      Helper II
      6 years ago

      Any reccomendation how to format each value field? I.e Measure 1 is a # and Measure 10 is a %. 

      Would i just include it in the "value" meaure? Like:

       

      Sumx(table,

      switch(table[id],

      1,format(Measure1,"0"),

      .....

      10,format(Measure10,"0.0%")

      ))

      • MFelix's avatar
        MFelix
        Super User
        6 years ago

        Hi jignaski18 ,

         

        You need to use the FORMAT function something similar to:

         

        elected Measure=
               SWITCH(
                  SELECTEDVALUE(MeasuresIdentication[ID]);
                  1; FORMAT([Measure1];"0,0%");
                  2; FORMAT([Measure2];"###.###);
                   ...;...;
                  99; FORMAT([Measure99];"###.### $")
                 )

        Be aware that the formatting need to be done outside the SUMX function otherwise it will try to sum text values and it will not work.

    • jignaski18's avatar
      jignaski18
      Helper II
      6 years ago

      MFelix This Solution worked. Measure Name in Column, Values in values, then conditional formatting for values (backround) selected value Conditional Formatting(measure).

       

      Values = 
SWITCH( SELECTEDVALUE(Table[MeasureName]),
      1,Measure 1,            
      2,Measure 2,
      ...,...,..,
     etc. etc.)


Conditional Formatting=
               SWITCH(TRUE();
                  SELECTEDVALUE(Table[MeasureName]) = 1 && [Measure1] >0 ; "GOLD";
                  SELECTEDVALUE(Table[MeasureName]) = 1 && [Measure1] <0 ; "RED";
                   ...;...;
                  SELECTEDVALUE(Table[MeasureName]) = 99 && [Measure99] > 0 ; "GOLD";
                  SELECTEDVALUE(Table[MeasureName]) = 99 && [Measure99] < 0 ; "RED"
                 )

       

      • MFelix's avatar
        MFelix
        Super User
        6 years ago

        Glad I could assist.

         

        Please mark the correct answer so that others can be helped.