cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Help with Avg measure

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 =

SWITCH(
TRUE(),
[01 TOT] = "","white",
[01 TOT] < Avg ALL [01 TOT] by year, "#red",
[01 TOT] > Avg ALL [01 TOT] by year, "#green",
)

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
2 REPLIES 2
Super User

@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

Helper I

Hi @amitchandak

Your formula's weren't calculating the average. I tried both:

CALCULATE(AVERAGEX('Table',[01 TOT]),ALLSELECTED('Table'[Vendor Code]))
and
CALCULATE(AVERAGEX('Table',[01 TOT]),ALLEXCEPT('Table','Table'[Year]))

but they both are returning the same incorrect results.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.