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

     

    Yes that is the way I'm suggesting, as refered if you place a format function inside the SUMX the values will get error since you are trying to make a sum of text values.

  • MFelix's avatar
    MFelix
    Super User
    6 years ago

    Hi jignaski18 ,

     

    you need to do :

    FORMAT (SUMX(......); "%")
  • jignaski18's avatar
    jignaski18
    Helper II
    6 years ago

    MFelix  Thanks for your fast responses. Is this the method you are suggesting? I also created a duplicate measure that removes the sumx funtion by using the selected value within the Switch function as seen below.

     

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

VERSUS

SUMX(TABLE,
     SWITCH(TABLE[ID]),