Conditional Formatting in Total and SubTotal

[Anonymous]

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 happening is that you are giving only the best value and the worst value in the Total column, however in the row below the sub total the values do not correspond to the best value in green and the worst in red.

I also wanted a way to add on the measure the best 3 lines in green and the worst 3 lines in red (in column Total and Sub Total), I tried to put it with the TOPN but it is not working to apply it correctly, can anyone help?

The measure I am using is as follows:

Cores = 
VAR MaxAmount = CALCULATE(MAXX(VALUES(Folha1[Técnico]),[Pago]),ALL(Folha1[Técnico]))
VAR MinAmount = CALCULATE(MINX(VALUES(Folha1[Técnico]),[Pago]),ALL(Folha1[Técnico]))
VAR LineAmonut = [Pago]
RETURN 
SWITCH (
    TRUE(),
    LineAmonut = MaxAmount, "GREEN",
    LineAmonut = MinAmount, "RED"
    )

Thanks!

[Anonymous]

Hi @MFelix,

That's right, this is how it works, however, in the subtotal line, the value of "Agosto" always appears always painted

Can I leave the .pbix file somewhere to help me? Are not letting me attach here to the answer

Edit: I upload the file .pbix to WeTransfer

Link: https://we.tl/t-pmicHS7lfQ

[MFelix · Accepted solution]

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.

[Anonymous]

@MFelix 

It resulted, thank you very much!

A hug, good job!

[Anonymous]

Hello @MFelix 

I try change the column "Sub" that you create by the column of Data which is the one I am using at the moment but it is not working. ☹️

Stays like this:

Ranking_ASC = 
IF (
    HASONEVALUE ( Folha1[Técnico] ),
    RANKX ( ALL ( Folha1[Técnico] ), CALCULATE ( SUM ( Folha1[Valor] ) ),, ASC ),
    RANKX ( ALL ( Folha1[Data] ), 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[Data] ), CALCULATE ( SUM ( Folha1[Valor] ) ),, DESC )
)


Cores = 
IF (
    NOT ( HASONEVALUE ( Folha1[Técnico] ) ) && NOT ( HASONEVALUE ( Folha1[Data] ) ),
    BLANK (),
    SWITCH ( TRUE (), [Ranking_DESC] <= 3, "Green", [Ranking_ASC] <= 3, "Red" )
)

[MFelix]

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]

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]

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:

[MFelix]

Hi @Anonymous ,

Create the following measures:

Ranking_desc = RANKX(ALL('Table'[Cat]), CALCULATE(SUM('Table'[Value])), ,DESC)

Ranking_asc = RANKX(ALL('Table'[Cat]), CALCULATE(SUM('Table'[Value])), ,ASC)


Background =
IF (
    HASONEVALUE ( 'Table'[Cat] ),
    SWITCH ( TRUE (), [Ranking] <= 3, "Green", [Ranking_asc] <= 3, "red" )
)

Now use the background formula for the background.

[Anonymous]

Hello @MFelix 

Thank you in advance for your help!

The measure works for the total column, however the subtotal line is always filled with a color I don't know why. Stay in this format (821€ in Agosto), can you help?

[MFelix]

Hi @Anonymous ,

Sorry for the error I did not understood that you also needed the subtotals in line :you need to redo your measures to the following code:

Ranking_asc =
IF (
    HASONEVALUE ( 'Table'[Cat] ),
    RANKX ( ALL ( 'Table'[Cat] ), CALCULATE ( SUM ( 'Table'[Value] ) ),, ASC ),
    RANKX ( ALL ( 'Table'[Sub] ), CALCULATE ( SUM ( 'Table'[Value] ) ),, ASC )
)


Ranking_desc =
IF (
    HASONEVALUE ( 'Table'[Cat] ),
    RANKX ( ALL ( 'Table'[Cat] ), CALCULATE ( SUM ( 'Table'[Value] ) ),, DESC ),
    RANKX ( ALL ( 'Table'[Sub] ), CALCULATE ( SUM ( 'Table'[Value] ) ),, DESC )
)


Background =
IF (
    NOT ( HASONEVALUE ( 'Table'[Cat] ) ) && NOT ( HASONEVALUE ( 'Table'[Sub] ) ),
    BLANK (),
    SWITCH ( TRUE (), [Ranking_desc] <= 3, "Green", [Ranking_asc] <= 3, "red" )
)

PBIX file attach.