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 the above table as a Matrix in Power BI:
MONTH | TICKETS UPDATED # | STOCK REPLENISHED # | INCREASED MPL / MDQ # |
May 2019 | 9 | 48 | 10 |
June 2019 | 11 | 47 | 9 |
July 2019 | 8 | 52 | 8 |
I would like to format as below:
I need to create a measure for each column, that will allow me to format as such:
IF( FOLLOWING MONTH Value > PREVIOUS MONTH Value , Green Color ,
IF( FOLLOWING MONTH Value < PREVIOUS MONTH Value , Red Color ))
Help would be really appreciated.
Regards,
Robin
Solved! Go to Solution.
Hi @Anonymous
I did an example for 1 column.
Create 2 new columns:
Previous Month Tickets = VAR prev_month = PREVIOUSMONTH ( T13[MONTH] ) RETURN IF ( CALCULATE ( SUM ( T13[TICKETS UPDATED #] ), FILTER ( T13, prev_month = T13[MONTH] ) ) = BLANK (), 0, CALCULATE ( SUM ( T13[TICKETS UPDATED #] ), FILTER ( T13, prev_month = T13[MONTH] ) ) )
Colors = IF ( T13[TICKETS UPDATED #] > T13[Previous Month Tickets], "GREEN", "RED" )
Then format according to field value
Cheers!
A
Hello @Anonymous
You can do this with measures that compare the prior month amount to the current month amount the return a color string based on the compare. I chose slightly lighter colors that the standard red and green.
Format Tickets Update = VAR PriorMonthAmt = CALCULATE( [TICKETS UPDATE #] , PARALLELPERIOD(Dates[Date],-1,MONTH ) ) VAR Compare = IF ( ISBLANK( PriorMonthAmt ), BLANK(), [TICKETS UPDATE #] - PriorMonthAmt) RETURN SWITCH ( TRUE(), Compare = 0, "none", Compare < 0, "#ff7f7f", Compare > 0, "#7fbf7f")
Format Stock Replenished = VAR PriorMonthAmt = CALCULATE( [STOCK REPLENISHED #] , PARALLELPERIOD ( Dates[Date],-1,MONTH ) ) VAR Compare = IF ( ISBLANK ( PriorMonthAmt ), BLANK(), [STOCK REPLENISHED #] - PriorMonthAmt) RETURN SWITCH ( TRUE(), Compare = 0, "none", Compare < 0, "#ff7f7f", Compare > 0, "#7fbf7f")
Format MPL / MDQ = VAR PriorMonthAmt = CALCULATE( [INCREASED MPL / MDQ #] , PARALLELPERIOD(Dates[Date],-1,MONTH ) ) VAR Compare = IF ( ISBLANK( PriorMonthAmt ), BLANK(), [INCREASED MPL / MDQ #] - PriorMonthAmt) RETURN SWITCH ( TRUE(), Compare = 0, "none", Compare < 0, "#ff7f7f", Compare > 0, "#7fbf7f")
Then you use the conditional formatting over the field
I'm struggling with the MONTH column formatting.
How do you get the MONTH column in acceptable date format for PARALLELPERIOD or PREVIOUSMONTH functions to work?
All good, I found the function that transforms text to date: DATEVALUE()
ALl sorted thanks guys 🙂
Hi @Anonymous
I did an example for 1 column.
Create 2 new columns:
Previous Month Tickets = VAR prev_month = PREVIOUSMONTH ( T13[MONTH] ) RETURN IF ( CALCULATE ( SUM ( T13[TICKETS UPDATED #] ), FILTER ( T13, prev_month = T13[MONTH] ) ) = BLANK (), 0, CALCULATE ( SUM ( T13[TICKETS UPDATED #] ), FILTER ( T13, prev_month = T13[MONTH] ) ) )
Colors = IF ( T13[TICKETS UPDATED #] > T13[Previous Month Tickets], "GREEN", "RED" )
Then format according to field value
Cheers!
A
Hello, I tired thre abovce, but when I go to conditional formatting aand then select format by field the box for based on field is red and it wont let me choose the field I creatd above.
Thanks mate, that's exactly what I'm looking for!
You can optimise the above to be in one column (one go),
I did it in 2 steps for better understanding the steps.
Good Luck!
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |