Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
anna-lee
Helper I
Helper I

Help with Avg measure

I currently have a matrix that looks like the below.

annalee_0-1669758468680.pngannalee_1-1669758537120.png

 

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 CodeYear05 Total Asset Turnover06 Current Ratio
CAP0012014  
CAP0012015  
CAP0012016 1.930710612
CAP00120173.5189261592.029477196
CAP00120181.8086538811.521665604
CAP00120191.7186340521.679110985
CAP0012020  
CAP0012021  
ENN0012014  
ENN0012015  
ENN0012016 1.608867851
ENN00120174.1687809082.05808682
ENN001201811.42269761.270856779
ENN001201910.885671774.741053777
ENN001202010.647593327.671194389
ENN00120218.6026098216.18038405
GOT0012014  
GOT0012015  
GOT0012016  
GOT0012017 1.7586807
GOT00120183.525662112.124413981
GOT00120193.0699926631.662603711
GOT00120202.2331831062.756393104
GOT00120213.4023362971.783105835
HLU0012014 1.224379444
HLU00120153.2124255251.274757327
HLU00120162.7511197161.430842194
HLU00120173.3763454231.34102308
HLU00120183.1336386621.503459097
HLU00120193.1708217191.835815342
HLU00120202.4532529262.651480975
HLU0012021  
JM30012014  
JM30012015  
JM30012016  
JM30012017 3.167344818
JM300120182.2460329112.384219389
JM300120192.0639184011.950582308
JM300120202.162035632.528987681
JM30012021  
PGNY0012014  
PGNY0012015 5.021096458
PGNY00120163.9950977953.09440364
PGNY00120174.2365190593.926147286
PGNY00120185.2782909491.756153928
PGNY00120194.6363668021.624023349
PGNY00120202.5383315581.554417275
PGNY0012021  
RCA0012014  
RCA0012015  
RCA0012016 3.767709675
RCA00120175.1266477021.624707333
RCA00120184.3208683471.143674575
RCA00120192.5451086851.181451109
RCA00120201.6805879380.896006701
RCA00120212.0913988222.574800288
2 REPLIES 2
amitchandak
Super User
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

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.

annalee_0-1669822964397.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors