topic I want a new column that is the difference between a value column and the mean of sliced data in DAX Commands and Tips

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

canders1 — Thu, 06 Feb 2025 15:55:11 GMT

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.

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

bhanu_gautam — Thu, 06 Feb 2025 16:08:09 GMT

canders1 , Try using

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

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

canders1 — Thu, 06 Feb 2025 16:12:30 GMT

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

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

canders1 — Thu, 06 Feb 2025 16:15:40 GMT

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

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

canders1 — Thu, 06 Feb 2025 16:18:49 GMT

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.

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

canders1 — Thu, 06 Feb 2025 16:23:42 GMT

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.

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

bhanu_gautam — Thu, 06 Feb 2025 16:34:02 GMT

canders1 , Can you share sample data or PBIX

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

canders1 — Thu, 06 Feb 2025 16:46:38 GMT

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.

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

canders1 — Thu, 06 Feb 2025 17:01:39 GMT

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

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

canders1 — Thu, 06 Feb 2025 17:09:21 GMT

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

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

canders1 — Thu, 06 Feb 2025 20:54:08 GMT

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])

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

Anonymous — Fri, 07 Feb 2025 03:07:56 GMT

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

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

canders1 — Fri, 07 Feb 2025 12:58:33 GMT

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

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

Anonymous — Mon, 10 Feb 2025 05:25:20 GMT

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

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

Poojara_D12 — Mon, 10 Feb 2025 05:40:59 GMT

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.

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

canders1 — Wed, 19 Feb 2025 15:40:22 GMT

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.