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,

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Yes, but it dosen't seems working correctly.

    I do have multiple column in the header that drill down and the measure hiding some of the values based on that.

    I realize that we can't do conditional formatting on sub total or total without including the detail rows.

    I gave up (Iv'e tried all the column in the matrix one by one and it desen't produce the expected results - maybe Microsoft will address that issue - conditional formatting is different between table and matrix)

    You don't have to spend time on this issue - you can close it - you see we can't even close an issue without mark it as solution 

    Thank you 

    Oded Dror

     

  • MFelix's avatar
    MFelix
    Super User
    7 years ago

    Hi Anonymous ,

     

    If you are abble to share a sample file I can take a look at it, because since the calculation is based on a measure the context can change the result.

     

    If you have sensative data please share it in private message.

     

    Regards,

    MFelix

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi there,

     

    I realize the calculation % (Actual Vs Budget) is wrong thats cause the formating to be wrong i need to calculate %
    based on condition of th ematrix then apply the conditional formating to it. Let me work on that and get back to you if I need more help.

     

    Thanks,

    Oded Dror

  • Anonymous's avatar
    Anonymous
    Not applicable
    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
    Super User
    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