Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHello @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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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 @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:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHello @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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |