How to find the average in the group

[Anonymous]

i want the average in the form

eg: cellular Therapy (39+15+4)/3(Years)

Can anyone help me out with this?

[Anonymous · Accepted solution]

[Within-Group Avg over Years] =
// T is the name of the table;
// This measure will return
// a number if only one group is
// visible in the current context.
var __currentGroup = selectedvalue( T[StudyGroup] )
var __result =
    calculate(
        averagex(
            values( T[Year] ),
            [AVGSdgGroupMeasure]
        ),
        T[StudyGroup] = __currentGroup,
        // This directive makes it
        // a visual calculation.
        allselected( T )
    )
return
    __result

[Anonymous]

Gives me error

[Anonymous]

You have to be able to say VALUES( T[Year] ). I don't know how you obtain this field from your table but you have to be able to do it for the formula to work.

[Anonymous]

Yeah... If you start reading the error messages, they will help you diagnose problems.

[Anonymous]

it says

"The value for 'Year' cannot be determined. Either the column doesn't exist, or there is no current row for this column."

I tried putting the actual year column it worked! thank you.

But I am getting the sum of the columns not the average

it should be 58/3(no. of years) for the first group. Please let me know if I am doing anything wrong

[Anonymous]

Mate, you're missing context transition under AVERAGEX. A measure is not the same as putting COUNT(something). A measure is ALWAYS implicitly wrapped in CALCULATE. Since you're not using a measure under AVERAGEX, you have to wrap this expression into CALCULATE yourself.

[Anonymous]

Worked! I learned something new today. Thank you

[Anonymous]

@Anonymous

Here's a piece of the best advice you can get about PowerBI and DAXing:

As soon as you can, please get yourself the book "The Definitive Guide to DAX" by Marco Russo and Alberto Ferrari and read it as fast as you can. You'll have to read it several times over so start as soon as you can.

This IS simply the best advice you've ever heard/read from a human being about mastering PowerBI, even though you might think at this very moment that I'm out of my mind.

[Greg_Deckler]

@Anonymous - Perhaps:

Measure = 
  VAR __Group = MAX([sdgStudyGroup])
  VAR __Average = AVERAGEX(FILTER(ALL('Table'),[sdgStudyGroup]=__Group),[Count of vw_AccrualsListPatientID])
RETURN
  __Average

[Anonymous]

It did not work @Greg_Deckler 

[Greg_Deckler]

@Anonymous - Oh, I think I see what is going on, it is a measure aggregation issue. This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

So probably something like:

Measure =
  VAR __Group = MAX([sdgStudyGroup])
  VAR __Table = SUMMARIZE(FILTER(ALL('Table'),[sdgStudyGroup] = __Group),[Year],"Count",[vw_AccrualsListPatientID])
RETURN
  AVERAGEX(__Table,[vw_AccrualsListPatientID)

[Anonymous]

Yes I want to find the average of the [Count of..] 

[Greg_Deckler]

@Anonymous - 

Oh, I think I see what is going on, it is a measure aggregation issue. This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

So probably something like:

Measure =
  VAR __Group = MAX([sdgStudyGroup])
  VAR __Table = SUMMARIZE(FILTER(ALL('Table'),[sdgStudyGroup] = __Group),[Year],"Count",COUNT([vw_AccrualsListPatientID]))
RETURN
  AVERAGEX(__Table,[Count])

This is why you should always post sample source data because it is hard to tell exactly what things are in images. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Anonymous]

It says i cannot find the name

[Greg_Deckler]

@Anonymous 

Measure =
  VAR __Group = MAX([sdgStudyGroup])
  VAR __Table = SUMMARIZE(FILTER(ALL('Table'),[sdgStudyGroup] = __Group),[Year],"Count",COUNT([vw_AccrualsListPatientID]))
RETURN
  AVERAGEX(__Table,[Count])

[Anonymous]

it gives me all the columns not the filtered one.

[Greg_Deckler]

@Anonymous - Dude, seriously, you need to include more details in your post if you want to get a valid answer.

Measure =
  VAR __Group = MAX([sdgStudyGroup])
  VAR __Table = SUMMARIZE(FILTER(ALLEXCEPT('Table',[Year]),[sdgStudyGroup] = __Group),[Year],"Count",COUNT([vw_AccrualsListPatientID]))
RETURN
  AVERAGEX(__Table,[Count])

[amitchandak]

@Anonymous ,

calculate([AvgSgdGroupMeasure], allexpect(table,Table[SdgStudygroup]))

[Anonymous]

sorry did not work