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 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

      That is what i did try, sorry for not clarifying that. Unfortunatly when I work outside of the SUMX, then my matrix returns empty rows for all regions(districts), groups(crwname), customers. 

      • MFelix's avatar
        MFelix
        Super User
        6 years ago

        Hi jignaski18 ,

         

        you need to do :

        FORMAT (SUMX(......); "%")