Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
I currently have a matrix that looks like the below.
I'm trying to write a measure to conditional format for the measure '01 TOT' that will compare it to the average of ALL the vendors (even when the matrix is filtered) across '01 TOT' by year.
Measure would probably work up to something like the below
Measure =
So ideally the average amount for 2018 '01 TOT' would be 4.53 and each individual row would compare off that number. Then CAP001 would be red, ENN001 would be green, etc.
Side note that '01 TOT' and '02 CR' are measures and not columns. The matrix has been sorted to switch values to rows.
Vendor Code | Year | 05 Total Asset Turnover | 06 Current Ratio |
CAP001 | 2014 | ||
CAP001 | 2015 | ||
CAP001 | 2016 | 1.930710612 | |
CAP001 | 2017 | 3.518926159 | 2.029477196 |
CAP001 | 2018 | 1.808653881 | 1.521665604 |
CAP001 | 2019 | 1.718634052 | 1.679110985 |
CAP001 | 2020 | ||
CAP001 | 2021 | ||
ENN001 | 2014 | ||
ENN001 | 2015 | ||
ENN001 | 2016 | 1.608867851 | |
ENN001 | 2017 | 4.168780908 | 2.05808682 |
ENN001 | 2018 | 11.4226976 | 1.270856779 |
ENN001 | 2019 | 10.88567177 | 4.741053777 |
ENN001 | 2020 | 10.64759332 | 7.671194389 |
ENN001 | 2021 | 8.60260982 | 16.18038405 |
GOT001 | 2014 | ||
GOT001 | 2015 | ||
GOT001 | 2016 | ||
GOT001 | 2017 | 1.7586807 | |
GOT001 | 2018 | 3.52566211 | 2.124413981 |
GOT001 | 2019 | 3.069992663 | 1.662603711 |
GOT001 | 2020 | 2.233183106 | 2.756393104 |
GOT001 | 2021 | 3.402336297 | 1.783105835 |
HLU001 | 2014 | 1.224379444 | |
HLU001 | 2015 | 3.212425525 | 1.274757327 |
HLU001 | 2016 | 2.751119716 | 1.430842194 |
HLU001 | 2017 | 3.376345423 | 1.34102308 |
HLU001 | 2018 | 3.133638662 | 1.503459097 |
HLU001 | 2019 | 3.170821719 | 1.835815342 |
HLU001 | 2020 | 2.453252926 | 2.651480975 |
HLU001 | 2021 | ||
JM3001 | 2014 | ||
JM3001 | 2015 | ||
JM3001 | 2016 | ||
JM3001 | 2017 | 3.167344818 | |
JM3001 | 2018 | 2.246032911 | 2.384219389 |
JM3001 | 2019 | 2.063918401 | 1.950582308 |
JM3001 | 2020 | 2.16203563 | 2.528987681 |
JM3001 | 2021 | ||
PGNY001 | 2014 | ||
PGNY001 | 2015 | 5.021096458 | |
PGNY001 | 2016 | 3.995097795 | 3.09440364 |
PGNY001 | 2017 | 4.236519059 | 3.926147286 |
PGNY001 | 2018 | 5.278290949 | 1.756153928 |
PGNY001 | 2019 | 4.636366802 | 1.624023349 |
PGNY001 | 2020 | 2.538331558 | 1.554417275 |
PGNY001 | 2021 | ||
RCA001 | 2014 | ||
RCA001 | 2015 | ||
RCA001 | 2016 | 3.767709675 | |
RCA001 | 2017 | 5.126647702 | 1.624707333 |
RCA001 | 2018 | 4.320868347 | 1.143674575 |
RCA001 | 2019 | 2.545108685 | 1.181451109 |
RCA001 | 2020 | 1.680587938 | 0.896006701 |
RCA001 | 2021 | 2.091398822 | 2.574800288 |
@anna-lee , Try like
Measure =
Var _tot = [01 TOT]+0
var _all = calculate([01 TOT], allselected(Table[Vendor]) // or calculate([01 TOT], removefilters(Table[Vendor])
SWITCH(
TRUE(),
_tot = 0,"white",
_tot < _all, "#red",
_tot > _all, "#green",
)
very similar what I have done here
Scatter Quadrant -Segment in 4 quadrants based on Margin % and Discount, Use Constant Line- https://youtu.be/0k_C_E7YOQY
Hi @amitchandak
Your formula's weren't calculating the average. I tried both:
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
11 | |
9 | |
8 | |
6 | |
5 |
User | Count |
---|---|
19 | |
17 | |
12 | |
9 | |
9 |