Dynamic replacement of values in a DAX meaure

[BIMJ]

Hi Experts

The requirement is to replace any value greater than 8 with the average of that particular row excluding the value greater than 8.

Then use that new average to replace the value greater than 8.

How to write a dax measure for it (and should be dynamic)?

First row is shown with expected result on the right.

Input:

Code	Blue	Green	Red	Black	Yellow	Average
A03W		24.19371	2.686653	3.115043	3.02382	8.254807
A024		3.779617	1.971338	4.070373	1.777928	2.899814
A163	8.74795	5.004857	3.173948	11.30612	2.667716	6.180117
A25Q		2.688676	1.304879		2.573394	2.188983
A62F		4.013494	2.167819		1.611555	2.597623
A6744		4.488213	2.156501		0.347077	2.330597
A77S		4.75	2.387261		5	4.045754
A795			2.26449		1.466317	1.865403

Output required

Showing only first row

Code	Blue	Green	Red	Black	Yellow	Average
A03W		2.941839	2.686653	3.115043	3.02382	2.941839

Here the green value (which is greater than 8 ) is replaced with the average of other 4 in the row.  Anything above 8 should be replaced by the average of other values in the row

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

Expected result measure: =
VAR _avg =
    AVERAGE ( Data[Value] )
VAR _colorlist =
    FILTER (
        ADDCOLUMNS (
            ALLSELECTED ( Color[Color], Color[Index] ),
            "@value",
                IF (
                    CALCULATE ( SUM ( Data[Value] ) ) > 8,
                    0,
                    CALCULATE ( SUM ( Data[Value] ) )
                )
        ),
        [@value] <> 0
    )
VAR _avgexceptovereight =
    AVERAGEX ( _colorlist, [@value] )
VAR _colorlistreplaceovereight =
    IF ( _avg > 8, _avgexceptovereight, _avg )
RETURN
    IF (
        HASONEVALUE ( Color[Color] ) && HASONEVALUE ( Code[Code] ),
        SWITCH (
            TRUE (),
            SELECTEDVALUE ( Color[Color] ) = "Average", _avgexceptovereight,
            _colorlistreplaceovereight
        )
    )

[BIMJ]

It works but why the total is not showing up?

[Jihwan_Kim]

Hi,

Thank you for your feedback.

In the measure, I purposely made total column and total row blank by adding HASONEVALUE DAX formula. Because I did not know what number to place there.

[BIMJ]

Thanks for the prompt reply. It would be great if I can get the total as - 

if any value >8, then the total should be the value used to replace the original value (in this case 2.94) else the original average. 

[Jihwan_Kim]

Hi,

Thank you for your message.

I am not sure if I understood your last question correctly, but is it something like the right side visualization below?

Please check the attached pbix file.

Thank you.

[BIMJ]

It is almost as expected! The code A163 should have the total 3.61 

Thank you

[Jihwan_Kim · Accepted solution]

Hi,

Sorry for not being careful.

Please check the attached file if it suits your requirement.

Expected result measure V2: =
VAR _avg =
    AVERAGE ( Data[Value] )
VAR _colorlist =
    FILTER (
        ADDCOLUMNS (
            ALLSELECTED ( Color[Color], Color[Index] ),
            "@value",
                IF (
                    CALCULATE ( SUM ( Data[Value] ) ) > 8,
                    0,
                    CALCULATE ( SUM ( Data[Value] ) )
                )
        ),
        [@value] <> 0
    )
VAR _avgexceptovereight =
    AVERAGEX ( _colorlist, [@value] )
VAR _colorlistreplaceovereight =
    IF ( _avg > 8, _avgexceptovereight, _avg )
RETURN
    IF (
        HASONEVALUE ( Code[Code] ),
        IF (
            HASONEVALUE ( Color[Color] ),
            _colorlistreplaceovereight,
            _avgexceptovereight
        )
    )