Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have a matrix that is a budget to actual comparision. My variance column is a meausre that i created. Then i have months data rolling into this depenidng on the month that is slected on the slicer. The fiscal year starts July so that month number is 1.
What i am trying to do it have the variance column highlight red if over the expected amount. The expected amount would simply be caulculated like if the slicer is ending in October (#4 month) then the excepted amount would be 4/12 or 33%. So if that percentage in the variance column is above 33% then it would be highlighted red. See below for pictures.
matrix
slicer
Any suggestions is greatly appreciated. thanks!
Solved! Go to Solution.
hi, @Anonymous
After my research, you could do these as below:
Step1:
this is variance measure
variance = CALCULATE(SUM(Table1[Revenue]))/CALCULATE(SUM(Table1[Revenue Budget]))
Then we add two more other measures
Number months = CALCULATE((DATEDIFF(MIN(Table1[Date]),MAX(Table1[Date]),MONTH)+1)/12,ALLEXCEPT(Table1,Table1[Date]))
Conditional = IF([variance]>[Number months],1,2)
Step2:
select matrix visual and right-click field variance, Conditional formatting ->Background color
Step3:
Select Format by Rules -> Based on field measure Conditional ->Rules if value is 1
Result:
here is my demo, please try it.
Best Regards,
Lin
hi, @Anonymous
After my research, you could do these as below:
Step1:
this is variance measure
variance = CALCULATE(SUM(Table1[Revenue]))/CALCULATE(SUM(Table1[Revenue Budget]))
Then we add two more other measures
Number months = CALCULATE((DATEDIFF(MIN(Table1[Date]),MAX(Table1[Date]),MONTH)+1)/12,ALLEXCEPT(Table1,Table1[Date]))
Conditional = IF([variance]>[Number months],1,2)
Step2:
select matrix visual and right-click field variance, Conditional formatting ->Background color
Step3:
Select Format by Rules -> Based on field measure Conditional ->Rules if value is 1
Result:
here is my demo, please try it.
Best Regards,
Lin
Thank you! That works great. And thanks for the indepth step by step. It was the nice finishing touch to the report.
User | Count |
---|---|
73 | |
70 | |
38 | |
24 | |
23 |
User | Count |
---|---|
96 | |
94 | |
50 | |
42 | |
40 |