Forum Discussion

LiloSnoopy115's avatar
LiloSnoopy115
New Member
3 years ago

Advanced Matrix formatting for alternate column colour

Hi All,

I’m a fresher at DAX Power BI. I want to do conditional formatting to show alternate column colour in a matrix visual.


I have created a matrix containing total sales by product in a row and by segment in the column. There is only one Financials fact table in my data model, no separate product dimension or segment dimension.


I have created a measure called “CF_Segment” to calculate the ranking of the column in a visual matrix that corresponds to the relative position of the individual column. Then use this measure as a rule to create the conditional format for the column in the matrix.

 

CF_Segment =
VAR SelectedSegment = SELECTEDVALUE(financials[Segment])
VAR RankTable = ALLSELECTED(financials[Segment])
VAR SegmentRank =
    RANKX(
        RankTable,
       financials[Segment],
        SelectedSegment,
        ASC,
        Dense
    )
VAR CF = IF(
    ISEVEN(SegmentRank), "Lightblue", "White")
RETURN
SegmentRank


The problem with this measure is not working perfectly, as some products only appear in some segments. As a result, some of the ranking values in the column are not correspond to the column position in the matrix visual. See attached screenshots 1 and 2.

 

Screenshot 1 - ranking values incorrect in the  Midmarket and Small Business segments.

 

Screenshot 2 - some products only have sales values in some segments.


Can anyone provide suggestions on how to enhance the codes in the measure so that it returns the correct ranking number that corresponds to the column position in the matrix visual even if the sale value of that product is zero in relation to that segment?

 

Many thanks in advance for your assistance in this matter.

 

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi LiloSnoopy115 ,

    Could you please provide some raw data in your table 'financials'(exclude sensitive data) with Text format, your visual Fields settings and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

    How to provide sample data in the Power BI Forum

     

    And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards