Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Row by row Conditional Formatting on a Matrix

Dear all, 

 

I have a matrix formating issue. 

I want to conditionally format my matirx row by row to show the monthly highest and smallest values for the years and months selected.

The result should look like this:

 

 

 

I can get the correct formating (below) for the individual dates but not for the Month, by using this DAX expression.

_data rank =
IF (
    NOT (ISBLANK ([Sum Ouput])),
    RANKX(     CALCULATETABLE(
        ADDCOLUMNS(
                SUMMARIZE(
                    Table,
                    Table[Factory Name],
                    Table[Start Date],
                    Table[Production],
                     Table[Machine],
                    Table[Ref],
                    'Date'[Date]
                ),
            "Summerised Data", [Sum Output]
        ),
        ALLSELECTED('Date'[Date])
    ),
[Sum Output], , , Dense
    )

 

 

 

Please can you help me rank the data so that I can see the ranking what the data is aggregated on a monthly basis rather than the granular daily values?

 

Let me know if you require more information. Many thanks in advance. 

 

tagging a few people to help. tamerj1, FreemanZ , amitchandak 

 

 

 

 

 

12 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi,

    Thank you for your reply and apologies for my delay in responding. 

    Please find a pbix file here with a sample data set: pbix file 

    Here is also an Excel file showing the row by row conditional formating that I want. 

    Ideally each row of data is conditionally formated based on the highest and lowest value in that row. This formatting should be maintained when I drill down to the day granualrity or drill up to month. 

    Many thanks in advance!

    • lbendlin's avatar
      lbendlin
      Super User
      3 years ago

      For each Machine and Year you want to find the highest and lowest monthly values? Or for each Machine you want to find the highest and lowest values regardless of the year? You would need to use ISINSCOPE() to figure out where in the date hierarchy you are.

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        For each machine and year, I want to format the matrix so that the value in each row is formated according to how big it is from smallest to largests, ignoring other rows of data. as in the image below: For example for machine 2, 2023 46,466 data is the smallest and 2021 385,213 is the biggest.

         

         

         

         

        I also what this for each month in a year - the value in each row should be formated from smallest to largest, ignoring other rows of data. Please see below example, for Machine two we get more detail than in the before image, we see that for 2022, June had the smallest data and March the largest.

         

         

         

        If I drill down then the values in each row again should be formated to show the smallest to biggest, ignoring other rows of data. In the below eaxmple, the daily granulariy show that for Machine 2, in March 2022, March 20 had the smallest data whilst March 14 had the largest data.

         

         

        Thank for for your reply. I will check out ISINSCOPE() function.