How do I take the output of a measure and use it in another equation and conditional formatting???

[KrColeman]

I used a measure to calculate Average Cost of airline tickets for the month of May for a section of our employees. I really would like to take the total $753.53 and show it in the table just as a constant number in a column, then calculate the variance (through quick measure average cost per employee minus average cost overall (ex: $543.33 - $753.53) to show employee 10102 is below average in his airfare purchase. Once I have the variance column, I'd like to conditional format based on the total average ($753.53), I could type that number into the conditional formatting but I'd rather use the measure so that when I add months and the total average for each month is different, I don't have to keep changing my conditional formatting manually. I can not find a video on how to use the output of my measure as a total and/or how to use the calculated output as highlight any value greater than 'Average Cost' measure.  My Measure below I created to get the average cost for airfare only (needed to filter down my Expense Type). Can someone please help!!! Thank you for the help in advance! 

Average Airfare =

CALCULATE(

    AVERAGE('May 2023'[Expense Amount]),

    'May 2023'[Expense Type] IN { "Airfare" }

)

[some_bih · Accepted solution]

Hi @KrColeman 

I called column Variance as difference of current row value and overall average 753,5 (I inserted dummy data to get average as you do). Please note that you use your average measure, mine is just example.
For conditional formatting for column varince check last picture below. I hope this help.

Variance =

VAR _currentrow=[Average cost dummy  data]

VAR _average=[M_avg] -- with dummy data average is 753,5

Return

_currentrow -

CALCULATE(

    [M_avg],

    ALL(Sheet1)

)

[some_bih]

HI @KrColeman few questions 🙂 :

--level of your data granularity: daily or monthly or...

--share you average measure definition

- in table / visaul there are only one Expense type (like Airfare) or other

- you want simple average per expense type FOR Employee and Month to be compared with overall average (everything) or ...

[KrColeman]

Hi @some_bih,!

Thank you for trying to assist. Yes my DAX broke it down to only airfare for the expense type which is what I want. It is monthly by employee. So in the table my output is average per employee then at the bottom is the total $753. I want to know how I use the total $753 to find the difference per employees average- this issue is the $753 total comes from a measure so the measure keeps applying to the employee, I want to get the total for all the employees separate and keep separate to find the variance. Additionally I want to use as conditional formatting if possible, so any employee average greater than $753, their average cost I would highlight red. Thanks again for the help!