Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Difference between average and selected value

How to subtract the responding value from the average score in 2021(In this small sanmple data,i.e average of bolding numbers) for all Name

 

sample data:

NameidDateScore
A11/9/20216
B1 1/7/20217
C1 2/8/20218
A21/8/20229
B2 1/6/202210

 

expected outcome:

-1
0
1
2
3
Need Help
Show and Tell
Tips and Tricks
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

    Expected outcome measure: =
VAR _avg2021 =
    AVERAGEX (
        ADDCOLUMNS (
            FILTER ( ALL ( Data ), YEAR ( Data[Date] ) = 2021 ),
            "@score", [Score measure:]
        ),
        [@score]
    )
RETURN
    IF ( HASONEVALUE ( Data[Name] ), [Score measure:] - _avg2021 )

1 Reply

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

    Expected outcome measure: =
VAR _avg2021 =
    AVERAGEX (
        ADDCOLUMNS (
            FILTER ( ALL ( Data ), YEAR ( Data[Date] ) = 2021 ),
            "@score", [Score measure:]
        ),
        [@score]
    )
RETURN
    IF ( HASONEVALUE ( Data[Name] ), [Score measure:] - _avg2021 )