Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Matrix conditional formating

    Posted 30 days ago
      |   view attached

    Hello, 

    I have a matrix table with a Year-Month Column and a column "Value", which I want to apply a conditional formatting based on the values of 2 slicers :  "Sales/Fct" slicer and "VERSION" Slicer.

    I have created  2 measures which returns 1 if selected value is Forecast and version is "202201" otherwise 2 for "Sales" and version "202201".

    format_V202201_t =
    VAR a =
        SELECTEDVALUE ( 'DATA_Test'[Sales/Fct]) = "forecast"
    VAR b =
        "202201" IN VALUES ( DATA_Test[Version])
    RETURN
        IF ( a && b, 1, 2 )

    format_V202202_t =
    VAR a =
        SELECTEDVALUE ( 'DATA_Test'[Sales/Fct]) = "forecast"
    VAR b =
        "202201" IN VALUES ( DATA_Test[Version])
    RETURN
        IF ( a && b, 1, 2 )
     

    The conditionnal formatting works well  when I selected only one version. Indeed, the color changes to blue from january 2022,  but when I select all the values in my slicers , I don't have the result I expected. I should have for the first column blue color from January 2022 and for the second column blue color from February 2022.





    For the Format_V202202_t : I get what I wanted







    I don't Have the good result that I must get :
    For the first column, I must have color Blue for "202201" and not green.


     

     Could someone help me please ?
    You will find attached my PBI.

     

    thank you 



    ------------------------------
    Mouad Lda
    ------------------------------

    Attachment(s)

    pbix
    PB_color.pbix   53 KB 1 version


  • 2.  RE: Matrix conditional formating

    Posted 29 days ago
    @Mouad L,
    The problem you are running into is that in 2022-01 you have both "Sales" and "Forecast" in the Sales/Fct column, so the SELECTEDVALUE function in your formula is not returning what you were expecting.  One way that you can solve this is to wrap the SELECTEDVALUE in a CALCULATE, and add a filter argument like so:

    format_V202201_t = 
    VAR a =
        CALCULATE(SELECTEDVALUE ( 'DATA_Test'[Sales/Fct]) = "forecast", DATA_Test[Version]="202201")
    VAR b =
        "202201" IN VALUES ( DATA_Test[Version])
    RETURN
        IF ( a && b, 1, 2 )
    
    
    format_V202202_t = 
    VAR a =
        CALCULATE(SELECTEDVALUE ( 'DATA_Test'[Sales/Fct]) = "forecast", DATA_Test[Version] = "202202")
    VAR b =
        "202202" IN VALUES ( DATA_Test[Version])
    RETURN
        IF ( a && b, 1, 2 )​


    Hope that helps.

    Kaz.



    ------------------------------
    Kaz Shakir
    Sr. Program Manager, Asset Planning
    TN
    ------------------------------



  • 3.  RE: Matrix conditional formating

    Posted 29 days ago
    Hi @Kaz Shakir,
    It works !! Thank you so much !

    I want to add other "versions" and I want to know if we can create one measure for the values and only one measure for format with all the versions for the matrix conditional formating !

    Thank you for your help !

    Best regards,


    ------------------------------
    Mouad L
    ------------------------------



  • 4.  RE: Matrix conditional formating

    Posted 29 days ago
    @Kaz Shakir  ​

    ------------------------------
    Mouad L
    ------------------------------



  • 5.  RE: Matrix conditional formating

    Posted 28 days ago
      |   view attached
    @Mouad L,
    There is a way to have just one measure to use for the conditional formatting.  I'm not sure if it will work for all of your use cases, but perhaps consider doing the following:

    1. Make another calculated column (not a measure), that refers to the [Sales/Fct] column you already created.  It looks like this:
    Sales/Fct Format = 
        SWITCH(
            DATA_Test[Sales/Fct],
            "forecast", 1,
            "Sales", 2,
            0
        )​

    2. Then, use a Matrix visual, rather than a table.  Place the Year-Month in the Rows well, Version in the columns well, and Value in the Values well:

    3. Then format the "Cell elements", turn on the "Background color", and set it like this:

    This should result in a similar output to what you currently have:
    You could also try placing the Year field and the Month field in the Rows well, instead of the Year-Month field, and you would get this:

    and if you click the little "+" in front of one of the Year's, then it will expand for that year, like this:

    You can see this in the attached .pbix file.

    Kaz.

    ------------------------------
    Kaz Shakir
    Sr. Program Manager, Asset Planning
    TN
    ------------------------------

    Attachment(s)

    pbix
    PB_color_v2.pbix   55 KB 1 version


  • 6.  RE: Matrix conditional formating

    Posted 28 days ago
    @Kaz Shakir,

    Thank you so much for your help , it works perfectly !
    Best regards


    ------------------------------
    Mouad L
    ------------------------------