Forum Discussion
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 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!
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.
10 Replies
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.
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?
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.
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" ) )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
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.
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
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.