Forum Discussion
Non-Consecutive Years % Growth
In the matrix visual below, I am trying to format the font color to green when there is a 10% or more increase and red when the is a decrease of 10% or more from the previous available year. Most font color changes are correct, but I am getting errors like the example below.
My Year column is formatted as Text
My Total Percent measure is formatted as a Percent:
Total Percent =
IF (
HASONEVALUE ( 'Fact'[2022.Number & Question] ),
SUMX ( 'Fact', 'Fact'[Percent] )
)
I have created the following measure for the cell elements font conditional formatting:
The Growth measure is formatted as a Percent
Growth =
VAR PrevDate =
MAXX (
FILTER ( ALL ( 'Fact'[Year] ), 'Fact'[Year] < SELECTEDVALUE ( 'Fact'[Year] ) ),
'Fact'[Year]
)
VAR PrevSales =
CALCULATE (
[Total Percent],
FILTER ( ALL ( 'Fact'[Year] ), 'Fact'[Year] = PrevDate )
)
RETURN
IF ( PrevSales <> 0 && [Total Percent] <> 0, [Total Percent] / PrevSales - 1 )
The CF Test is formatted as a Whole number
CF-Test =
IF ( [Growth] >= 0.10, 1, IF ( [Growth] <= -0.10, 2 ) )
What is wrong with my measure/s that is causing the font color format to incorrectly?
3 Replies
- lbendlin
Super User
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 - parry2k
Anonymous can you drop the growth measure in the matrix visual and see if you are getting the correct calculation?
👉 Learn Power BI and Fabric - subscribe to our YT channel - @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤️
- parry2k
Anonymous I think your CF-TEST measure should be:
CF-Test = VAR __growth = [Growth] / 100 RETURN IF ( __growth >= 0.10, 1, IF ( __growth <= -0.10, 2 ) )👉 Learn Power BI and Fabric - subscribe to our YT channel - @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤️
