cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors