cancel
Showing results for
Did you mean: Helper III

## How do I take the average of a measure?

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

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.
7 REPLIES 7 Super User IV

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.

Regards,

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated! Helper III

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.

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:

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. Super User IV

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

Regards,

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated! Helper III

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(). Super User IV

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated! Helper III

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.

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

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

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. Community Support

Hi @mattbstrong,

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.   