topic Re: How to find the average in the group in DAX Commands and Tips

How to find the average in the group

Anonymous — Wed, 02 Sep 2020 03:12:03 GMT

i want the average in the form

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

Can anyone help me out with this?

Re: How to find the average in the group

amitchandak — Wed, 02 Sep 2020 03:25:10 GMT

Anonymous ,

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

Re: How to find the average in the group

Greg_Deckler — Wed, 02 Sep 2020 03:27:04 GMT

Anonymous - Perhaps:

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

Re: How to find the average in the group

Anonymous — Wed, 02 Sep 2020 03:33:40 GMT

It did not work Greg_Deckler

Re: How to find the average in the group

Anonymous — Wed, 02 Sep 2020 03:35:11 GMT

sorry did not work

Re: How to find the average in the group

Greg_Deckler — Wed, 02 Sep 2020 03:40:32 GMT

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)

Re: How to find the average in the group

Anonymous — Wed, 02 Sep 2020 03:39:49 GMT

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

Re: How to find the average in the group

Greg_Deckler — Wed, 02 Sep 2020 03:42:36 GMT

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.

Re: How to find the average in the group

Anonymous — Wed, 02 Sep 2020 03:45:38 GMT

It says i cannot find the name

Re: How to find the average in the group

Greg_Deckler — Wed, 02 Sep 2020 03:46:30 GMT

Anonymous

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

Re: How to find the average in the group

Anonymous — Wed, 02 Sep 2020 03:50:41 GMT

it gives me all the columns not the filtered one.

Re: How to find the average in the group

Greg_Deckler — Wed, 02 Sep 2020 04:05:32 GMT

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

Re: How to find the average in the group

Anonymous — Wed, 02 Sep 2020 09:55:45 GMT

[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

Re: How to find the average in the group

Anonymous — Wed, 02 Sep 2020 10:03:55 GMT

Gives me error

Re: How to find the average in the group

Anonymous — Wed, 02 Sep 2020 10:14:35 GMT

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

Re: How to find the average in the group

Anonymous — Wed, 02 Sep 2020 10:21:19 GMT

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

Re: How to find the average in the group

Anonymous — Wed, 02 Sep 2020 10:21:36 GMT

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.

Re: How to find the average in the group

Anonymous — Wed, 02 Sep 2020 10:23:40 GMT

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.

Re: How to find the average in the group

Anonymous — Wed, 02 Sep 2020 10:26:23 GMT

Worked! I learned something new today. Thank you

Re: How to find the average in the group

Anonymous — Wed, 02 Sep 2020 10:33:19 GMT

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.