cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper IV
Helper IV

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

 

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

 

Regards,

Pat





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

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


@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.

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





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

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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().

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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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.

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.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors