Help with Avg measure

[anna-lee]

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

[amitchandak]

@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

[anna-lee]

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.