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.
Hi,
I have a monthly pipeline report that shows how opportunities are progressing through a sales process. So far, thanks to help from this community, I have manipulated my source data in Power BI to the point where I have the exact the table I require.
It is a table that summarises the value of projects, split by sector, that have passed through each phase in a given month. And also the target values.
Here is a dummy PBIX file that has an example table in. https://1drv.ms/u/s!AtJBzwB0OS_Qgz_17trIoDazkoht?e=udfkfV
(Thank you again to @parry2k for the initial solution.)
The final step that I am trying to implement is to apply conditional formatting so each actual is green if it is equal or greater than the target, and red if not.
I have tried to apply conditional formatting rules but haven't been able to get it to work. I can't even get the targets to remain white.
I think I need to create an interim table that has ones and zeros, and then use the 'Format by' 'Rules' and 'Based on field' to look at the interim table. I've done this in Excel and then imported to Power BI, but that's not a solution I can use practically. I'm just not sure how to manipulate the data in the same way in Power BI.
Thank you in advance for any help.
Solved! Go to Solution.
Hi @Ptown ,
You could create a measure to configure it.
Measure =
VAR a =
CALCULATE (
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER ( 'Table', 'Table'[Target or actual?] = "Actual" )
),
ALLEXCEPT ( 'Table', 'Table'[Sector], 'Table'[Phase] )
)
VAR b =
CALCULATE (
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER ( 'Table', 'Table'[Target or actual?] = "Target" )
),
ALLEXCEPT ( 'Table', 'Table'[Sector], 'Table'[Phase] )
)
RETURN
IF (
a - b < 0
&& SELECTEDVALUE ( 'Table'[Target or actual?] ) <> "Target",
"red",
IF ( SELECTEDVALUE ( 'Table'[Target or actual?] ) = "Target", "white", "green" )
)
Here is my test file for your reference.
Hi @Ptown ,
You could create a measure to configure it.
Measure =
VAR a =
CALCULATE (
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER ( 'Table', 'Table'[Target or actual?] = "Actual" )
),
ALLEXCEPT ( 'Table', 'Table'[Sector], 'Table'[Phase] )
)
VAR b =
CALCULATE (
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER ( 'Table', 'Table'[Target or actual?] = "Target" )
),
ALLEXCEPT ( 'Table', 'Table'[Sector], 'Table'[Phase] )
)
RETURN
IF (
a - b < 0
&& SELECTEDVALUE ( 'Table'[Target or actual?] ) <> "Target",
"red",
IF ( SELECTEDVALUE ( 'Table'[Target or actual?] ) = "Target", "white", "green" )
)
Here is my test file for your reference.
@v-eachen-msft Thank you very much! That is great and I have been able to apply it to my real data.
Do you know how I can make the blank actual cells be red as well?
In my example there were no opportunities contributing towards the actual for Sector-B, phase 3. The target in the example was $3,100). The actual cell in the table is blank and has no colour in your solution. I'd like it be red (and I don't mind whether it shows a zero or is blank).
Covering 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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |