I want a new column that is the difference between a value column and the mean of sliced data

[canders1]

I have tried nearly everything I can think of for this and have run out of ideas.  I have a column called values and desire to have a new column that is the value minus the mean of the column.  I would like this to be dynamic and change with my slicers.  I have put this in to bing chat and Chat DPT and none of them output things that work.  I keep getting circular dependancy errors.  I start with a measure

Mean =

AVERAGE(CERT_DATA[VALUE])

Then I have tried a calculated column which is below which generates a circular dependancy error

MEAN COLUMN =

CERT_DATA[VALUE]-[Mean]

I tried creating a measure 

mean alt = CALCULATE(SUMX(CERT_DATA,CERT_DATA[VALUE]-[Mean]))  The result is zero for every line so this is not correct.

I tried creating a separate table below but for some reason I get a value that is not the mean.  It is close. but off by a couple digits.  I am not real familiar with summarize table so I was thinking that I would use the column that I am slice on which is spec as the driver for the summarized table.

Meantable = summarize(CERT_DATA,CERT_DATA[SPEC],"consolidation column",AVERAGE(CERT_DATA[VALUE]))

I have been stuck on this for too long and looking for suggestions that can get me moving again.

[Poojara_D12 · Accepted solution]

Hi @canders1 

You're on the right track, but the key issue here is that you cannot reference a measure in a calculated column because calculated columns are computed at the row level during data load and do not dynamically update with slicers. To achieve a dynamically updating mean subtraction that respects slicers, you must use measures instead of calculated columns.

Value Minus Mean = 
VAR CurrentValue = SELECTEDVALUE(CERT_DATA[VALUE]) 
VAR MeanValue = [Mean]
RETURN
    CurrentValue - MeanValue

This will dynamically adjust based on the slicers and filters applied.

[canders1]

I found one thing to help.  If I make a measure with the following

Mean =

CALCULATE(AVERAGE(CERT_DATA[VALUE]),ALL(CERT_DATA)) it will fill the column with the correct mean.  Then I created another measure 

difference =

CALCULATE(AVERAGE(CERT_DATA[VALUE])-[Mean])

[v-yiruan-msft]

Hi @canders1 ,

Thank you for your feedback and for sharing the measure formulas. Have you found a solution? If so, could you please mark your reply as "Answered"? This will greatly assist others in the community in finding the solution more easily. Thank you.

Best Regards

[canders1]

The information I found is only a solution for some cases.  Unfortunately when I tried it on a more involved table with slicers it fails because calculated columns are row by row

[v-yiruan-msft]

Hi @canders1 ,

The calculated column will not change according to the user interaction(slicer, filter, column selections etc.) in the report as the value of a calculated column is computed during data refresh and uses the current row as a context... Please review the following links about the difference of calculated column and measure...

Calculated Columns and Measures in DAX

Calculated Columns vs Measures

If it is convenient, could you please provide some raw/fake data in your tables(exclude sensitive data) with Text format and your expected result with backend logic and special examples base on your scenario? It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[canders1]

https://drive.google.com/file/d/12CatAsDkjP1e-xh-R_GyzZ4Zfvh0eEux/view?usp=sharing

Here is the file.  I am still not able to subtract a mean value from a column value even using measures.  I am not sure how to make this work.   

[canders1]

Just tried that.  I created the formula as a measure.  It resulted in all zeros.

[bhanu_gautam]

@canders1 , Try using

DAX
Difference =
VAR MeanValue = AVERAGE(CERT_DATA[VALUE])
RETURN
SUMX(CERT_DATA, CERT_DATA[VALUE] - MeanValue)

[canders1]

Tried the syntax as a measure which resulted in Zeros.  Then tried it as calculated column which resulted in a circular dependancy error.

[canders1]

Actually the calculated column version is not a circular dependancy after I fixed something.  However it doesn't work correctly because it is based on all the data and not filtered/sliced data.  It is better but it seems that I need to use a measure.  

[canders1]

Here are the columns.  The mean is 29.1.  The difference column is based on a the syntax provided as a measure.  The difference column is based on a calculated column.

[bhanu_gautam]

@canders1 , Can you share sample data or PBIX

[canders1]

I tried to attach a file that simulates this very simply and it says file type .pbix is not supported.

[canders1]

I just created a very simple table of 6 numbers and it doesn't work.

Just did a simple excel sheet with .2,.25, .21,.3, and .22 and attached the data to a new power bi report.   I tried it with a measure and a calculated column.  Here is the resultant table

[canders1]

I will not be able to because it is supplier data and would be confidential information.  I may be able to create a dummy file that is similar maybe.