How do I take the average of a measure?

[Anonymous]

I have a measure that does calculations on rows in some columns and then returns values in a new column.

I now need to take the average of that measure and display it in a second new column. I treid the following but it didn't work.

Task Score = If('00AB-Raw'[FIN Measure] < 0,

      If('00AB-Raw'[FIN Measure] < sum('Sys_Vars'[Upper Cap(Days)]) * (-1),

            Sum('00AB-Raw'[Start %]) + sum('Sys_Vars'[Upper Cap(Days)]) * SUM('Sys_Vars'[Daily Rate]),

            Sum('00AB-Raw'[Start %]) - '00AB-Raw'[FIN Measure] * SUM('Sys_Vars'[Daily Rate])

      ),

      IF('00AB-Raw'[FIN Measure] > sum('Sys_Vars'[Lower Cap(Days)]),

            0,

            Sum('00AB-Raw'[Start %]) - '00AB-Raw'[FIN Measure] * SUM('Sys_Vars'[Daily Rate])

            )

      )

Avg Task Score = AVERAGEX('00AB-Raw', '00AB-Raw'[Task Score])

I know that once I get this to work I will have the same value in every row in the second new column. This is fine for now because once I get this to work, I will need to group these rusults by the values in a different column.

[mahoneypat]

What are the columns in your visual?  Typically, this is done with an expression like this (replace with your table and column name, or add multiple columns to the ALLSELECTED.

Avg Task Score = AVERAGEX(ALLSELECTED(Table[Column]), [Task Score])

Regards,

Pat

[Anonymous]

@mahoneypat 

That worked to get the AVG Task Score to calculate. I now have a column with tha same value in every row. The value is equal to the average of Task Score.

Do you have any thoughts on how to group the results so that I get a differnet value for each group?  In other words, I have a table with the following. The Task Score is a measure.

A-----B-----C-----Group-----Task Score

1-----1-----1-----A-----------3

2-----1-----2-----A-----------5

1-----3-----0-----B-----------4

2-----2-----2-----B-----------6

I need the AVG Task Score to look like this:

A-----B-----C-----Group-----Task Score-----AVG Task Score

1-----1-----1-----A-----------3---------------4

2-----1-----2-----A-----------5---------------4

1-----3-----0-----B-----------4---------------5

2-----2-----2-----B-----------6---------------5

Ultimately, I have groups and each group gets a score on certian tasks. I need to show the average score per group.

[mahoneypat]

Please try this expression

Group Avg =
CALCULATE (
    SUMX (
        SUMMARIZE (
            Table,
            Table[A],
            Table[B],
            Table[C]
        ),
        [Task Score]
    ),
    ALL (
        Table[A],
        Table[B],
        Table[C]
    )
)

Regards,

Pat

[Anonymous]

I am not following. I am not sure what to use for Table[A], Table[B], Table[C]. My task score measure has conditional statments in it. I also don't understand how the column Group is taken into account.

Does groupby work? I tried the following but get an error.

Group Avg = groupby('00AB-Raw','00AB-Raw'[PROJ Owner],"Score",AVERAGEX(ALLSELECTED('00AB-Raw'),[Task Score]))

The error I get is "Function 'GROUPBY' scalar expressions have to be Aggregation functions over CurrentGroups(). The expression of each Aggregation has to be either a constant or directly reference the columns in CurrentGroup().

[mahoneypat]

I thought A B and C were columns in your visual.  If you do have columns other than the Group column, you can use the same pattern.  Summarize over the non-group columns, and remove the filters from the non-group columns with ALL().

Regards,

Pat

[Anonymous]

I think I might have confused things when I tried to simplify things in the example of my table. This is closer to what I am really looking at.

Attribute 1-----Attribute 2-----Attriubute 3-----Group-----Task Score-----AVG Task Score

Buy Book------Amanda--------1/1/2021--------A-----------3---------------4.5

Sell Book------John------------2/1/2021--------A-----------5---------------4.5

Fly Home-----Tom-------------11/14/2020------B-----------4---------------4.5

Win Game----Sam-------------10/12/2020------B-----------6---------------4.5

Task Score and AVG Task Score are Meaures.

I need a third measure that caclulates AVG Task Score per Group. For example:

Attribute 1-----Attribute 2-----Attriubute 3-----Group-----Task Score-----AVG Task Score---Group Score

Buy Book------Amanda--------1/1/2021--------A-----------3---------------4.5-----------------4

Sell Book------John------------2/1/2021--------A-----------5---------------4.5-----------------4

Fly Home-----Tom-------------11/14/2020------B-----------4---------------4.5-----------------5

Win Game----Sam-------------10/12/2020------B-----------6---------------4.5-----------------5

NOTE: There are over 10 attribute columns.

[v-shex-msft]

Hi @Anonymous,

In fact, power bi not suitable to write DAX formula to value across multiple fields dynamically, you need to hard code in your expressions.
In my opinion, I'd like to suggest you consider using the 'unpivot columns' feature to convert your value fields to 'attribute' and 'value'.

Unpivot columns (Power Query) 
Then you can simply write formulas to calculate across raw table fields and create matrix visual to display them.

BTW, if you want to do aggregate with measure formulas, you can use the summarize function to create a template table with the current category and measure formal, then you can use the iterator function to apply second aggregate functions.

Measure Totals, The Final Word 
Regards,

Xiaoxin Sheng