Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Conditional Formatting in Total and SubTotal

Hey guys,   I needed help here on this conditional formatting on this matrix table. I needed this dax measure to give me the best 3 results in green and the worst 3 results in red.   What is happ...
Need Help
Show and Tell
Tips and Tricks
  • MFelix's avatar
    MFelix
    5 years ago

    Hi Anonymous ,

     

    That has to do with the way I setup the formula that was using HASONEVALUE since Agosto was only one value it returned the green.

     

    I have change the formulas to:

     

    Ranking_asc = 
IF (
    ISINSCOPE( 'Folha1'[Técnico] ),
    RANKX ( ALLSELECTED( 'Folha1'[Técnico]),CALCULATE(SUM(Folha1[Valor])),, ASC ),
    RANKX ( ALLSELECTED(Folha1[Mês], Folha1[MonthNo]),CALCULATE(SUM(Folha1[Valor])),, ASC )
)

Ranking_desc = 

IF (
    ISINSCOPE(  'Folha1'[Técnico] ),
    RANKX ( ALLSELECTED( 'Folha1'[Técnico]),CALCULATE(SUM(Folha1[Valor])),, DESC ),
    RANKX ( ALLSELECTED( Folha1[Mês], Folha1[MonthNo]),CALCULATE(SUM(Folha1[Valor])),, DESC )
)

Cores = 
IF(ISINSCOPE(Folha1[Mês]) || ISINSCOPE(Folha1[Técnico]),
    SWITCH ( TRUE (), [Ranking_desc] <= 3, "Green", [Ranking_asc] <= 3, "red" )
)

     

    This changes will also make it work when you have filters on your data that the previous version did not had.

    PBIX file attach.

MFelix's avatar
MFelix
Super User
5 years ago

Hi Anonymous ,

 

Is the column DATA a date column or a month column how do you have the information setup, also is the month presented on top of the table coming from a hierarchy of a date column?

 

Is it possible to share a smal sample your table is it similar to the one below?

Técnico - Data - Valor

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi MFelix,

 

The Data comes from a date column, that is, the month in the table matrix is from the hierarchy of Data.

 

I leave here two images for better perception.

 

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    Hi Anonymous ,

     

    Working with the auto Datetime will give severall problems that's why you have several post advocating that you don't activate this type of feature.

     

    In this case you simply need to add two columns and use them on your measures and visualizations:

    MonthNo = MONTH('Table'[Date])

Month Name = FORMAT('Table'[Date], "mmmm")

    You need to sort the Month name by the monthNo column.

     

    Now redo your measures to:

     

    Ranking_asc = 
IF (
    HASONEVALUE ( 'Folha1'[Técnico] ),
    RANKX ( ALL ( 'Folha1'[Técnico] ), CALCULATE ( SUM ( 'Folha1'[Valor] ) ),, ASC ),
    RANKX ( ALL ('Folha1'[Month Name], 'Folha1'[MonthNo]), CALCULATE ( SUM ( 'Folha1'[Valor] ) ),, ASC )
)


Ranking_desc = 
IF (
    HASONEVALUE ( 'Folha1'[Técnico] ),
    RANKX ( ALL ( 'Folha1'[Técnico] ), CALCULATE ( SUM ( 'Folha1'[Valor] ) ),, DESC ),
    RANKX ( ALL('Folha1'[Month Name], 'Folha1'[MonthNo]), CALCULATE(SUM('Folha1'[Valor])) ,, DESC )
)

Background = 
IF (
    NOT ( HASONEVALUE ( 'Folha1'[Técnico] ) ) && NOT ( HASONEVALUE ( 'Folha1'[Month Name] ) ),
    BLANK (),
    SWITCH ( TRUE (), [Ranking_desc] <= 3, "Green", [Ranking_asc] <= 3, "red" )
)

     

    Now if you use the Month Name column on your matrix everything should work properly: