Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

dynamic conditinal formatting based on mtrix condition

Hi there, When you have a martix with Actuals and Budget columns the precentages and color coding make sense when the matrix colapsed but when you expand the matrix it dosen't make sense to have col...
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi there,

     

    This one works and I did conditional formatting based on that

     

    Formatting =
    VAR Percentage = [Actual Vs Budget]
    VAR CheckExpand =  Not(HASONEVALUE(GLedger[Month Name])) ||
        Not(HASONEVALUE(GLedger[GL_ACCT_TYPE])) || NOT(HASONEVALUE(GLedger[DD]))
    RETURN
    If(CheckExpand = TRUE(),
       Percentage,0)
    Thanks
    Oded Dror
  • MFelix's avatar
    MFelix
    7 years ago

    Hi Anonymous ,

     

    Glad I could give some pointers to get the solution don't forget to mark your result as the answer for this post to help others.

     

    Regards,

    MFelix

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi there,

 

You are checking the condition of the matrix if is expaned or collapsed that's one thing

The second thing is if it collapsed (then check) and subToal is over Budget below 10% then yellow over 10% red

And when the matrix is expanded don't show the background color, most likely I need to add calculate value too
to the IF statement, can you incorporate multiple IF statements and show me how to achive that? I need help on that.

 

Thanks,

Oded dror

 

MFelix's avatar
MFelix
Super User
7 years ago

Hi Anonymous ,

 

You can use a SWITCH function that evaluates an expression against a list of values and returns one of multiple possible result expressions.

 

Formatting =
VAR Percentage =
    ( SUM ( 'Table'[Value] ) / SUM ( 'Table'[Target] ) ) - 1
VAR CheckExpand =
    NOT ( HASONEVALUE ( 'Table'[SubCat] ) )
RETURN
    SWITCH (
        TRUE ();
        CheckExpand
            && Percentage < 0,1; "Yellow";
        CheckExpand
            && Percentage > 0,1; "Red"
    )

You can change the Percentage formula by the one you think is more suitable to your calculations.

 

Regards,

MFelix

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi there,

    this is my calculation - no error there but when I put this measure in my marrix I'm getting this error (see below)

    Formatting =
    VAR Percentage =
       Divide(((Sum('ActualVsBudget'[Activity_YTD]) - Sum(ActualVsBudget[BUD_YTD]))) , Sum(ActualVsBudget[BUD_YTD]) )
    VAR CheckExpand =
        NOT ( HASONEVALUE ( 'GLedger'[Account]) )
    RETURN
        SWITCH (
            TRUE(),
            CheckExpand
                && Percentage < 0,0.1, "Yellow",
            CheckExpand
                && Percentage > 0,0.1, "Red"
        )
     

    Error Message:
    MdxScript(Model) (34, 31) Calculation error in measure 'GLedger'[Formatting]:
    Function 'SWITCH' does not support comparing values of type True/False with values of type Number.
    Consider using the VALUE or FORMAT function to convert one of the values.
     
    Thanks,
    Oded Dror
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi there,

       

      I modify the measure and got it to work but it still showing flags when th ematrix is expandad 

       

      Formatting =
      VAR Percentage = [Actual Vs Budget]
      VAR CheckExpand =
          NOT ( HASONEVALUE ( 'GLedger'[DD]) )
      RETURN
          SWITCH (
              TRUE(),
              CheckExpand
                  && Percentage >= 0.0 && Percentage <= 0.10, UNICHAR(128077),
              CheckExpand
                  && Percentage > 0.10 && Percentage < 1.0,  UNICHAR(128681)
          )
      Thanks,
      Oded Dror
      • MFelix's avatar
        MFelix
        Super User
        7 years ago

        Hi Anonymous ,

         

        Is the 'GLedger'[DD] the lowest level of your matrix? is it the last level when you expand your matrix?

         

        Can you share a sample file?

         

        Regards,

        MFelix