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

Create Average Column using criteria from another column

I have some sports data (NBA) that I am looking to create positional averages from.  Below is an example of the dataset:

 

TeamPositionPTS.Avg
DETC18.5
DETPG25.2
ATLC21
ATLPG

23.7

 

I need to be able to Create averages for each Position (Across All Teams) to show the average PTS per position.  The final table would look like this (except with all teams/positions:

 

TeamPositionPTS.AvgPOS.Avg
ATLC2119.75
DETC18.519.75
ATLPG23.724.45
DETPG25.224.45

 

Essentially running an Average based upon the Position and then returning that average to the POS.Avg column.

 

The purpose is to then take that data and determine a Defense Vs. Position number (like: POS.AVG / PTS.AVG).  I've been working on this for a while and just cannot figure out how to get it done 😞

 

I very much appreciate any help!  Thank you.

3 ACCEPTED SOLUTIONS
Helper I
Helper I

I ended up doing the following:

 

GroupedRows = Table.Group(#"Reordered Columns1", {"Position"}, {{"POS.AVG", each List.Average([PTS.Avg]), type nullable number}}),
#"MergedQueries" = Table.NestedJoin(#"Reordered Columns1",{"Position"},GroupedRows,{"Attribute"},"DvP",JoinKind.Inner)

 

Those 2 with a calc column worked great.

 

View solution in original post

Super User IV
Super User IV

Just add a DAX calculated column with this formula

 

Avg Column = CALCULATE(AVERAGE(Table[Pts.Avg]), ALLEXCEPT(Table, Table[Position]))

 

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


View solution in original post

Community Support
Community Support

Hi @Covington ,

 

If you want to create that column in Power Query Editor, you can also try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnENUdJRcgZiQws9U6VYHZhQgDuQMDLVMwKLOYb4QJUZGSIJQBQZ65krxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Team = _t, Position = _t, PTS.Avg = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Team", type text}, {"Position", type text}, {"PTS.Avg", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Position"}, {{"Avg", each List.Average([PTS.Avg]), type nullable number}, {"All", each _, type table [Team=nullable text, Position=nullable text, PTS.Avg=nullable number]}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Team", "PTS.Avg"}, {"Team", "PTS.Avg"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded All",{"Team", "Position", "PTS.Avg", "Avg"})
in
    #"Reordered Columns"

avg.gif

 

If you want to use DAX to create a calculated column or a measure, try this:

Calculated column:

POS.Avg Column 1 = 
AVERAGEX (
    FILTER (
        'Table (2)',
        'Table (2)'[Position] = EARLIER ( 'Table (2)'[Position] )
    ),
    [PTS.Avg]
)
POS.Avg Column 2 = 
CALCULATE (
    AVERAGE ( 'Table (2)'[PTS.Avg] ),
    FILTER (
        'Table (2)',
        'Table (2)'[Position] = EARLIER ( 'Table (2)'[Position] )
    )
)

column.JPG

 

Measure:

POS.Avg Measure 1 = 
AVERAGEX (
    FILTER (
        ALLSELECTED ( 'Table (2)' ),
        'Table (2)'[Position] = MAX ( 'Table (2)'[Position] )
    ),
    [PTS.Avg]
)
POS.Avg Measure 2 = 
CALCULATE (
    AVERAGE ( 'Table (2)'[PTS.Avg] ),
    FILTER (
        ALLSELECTED ( 'Table (2)' ),
        'Table (2)'[Position] = MAX ( 'Table (2)'[Position] )
    )
)
POS.Avg Measure 3 = 
CALCULATE (
    AVERAGE ( 'Table (2)'[PTS.Avg] ),
    ALLEXCEPT ( 'Table (2)', 'Table (2)'[Position] )
)

measure.JPG

 

 

Best regards

Icey

 

If this post helps, then consider Accepting it as the solution to help other members find it faster.

View solution in original post

4 REPLIES 4
Community Support
Community Support

Hi @Covington ,

 

If you want to create that column in Power Query Editor, you can also try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnENUdJRcgZiQws9U6VYHZhQgDuQMDLVMwKLOYb4QJUZGSIJQBQZ65krxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Team = _t, Position = _t, PTS.Avg = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Team", type text}, {"Position", type text}, {"PTS.Avg", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Position"}, {{"Avg", each List.Average([PTS.Avg]), type nullable number}, {"All", each _, type table [Team=nullable text, Position=nullable text, PTS.Avg=nullable number]}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Team", "PTS.Avg"}, {"Team", "PTS.Avg"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded All",{"Team", "Position", "PTS.Avg", "Avg"})
in
    #"Reordered Columns"

avg.gif

 

If you want to use DAX to create a calculated column or a measure, try this:

Calculated column:

POS.Avg Column 1 = 
AVERAGEX (
    FILTER (
        'Table (2)',
        'Table (2)'[Position] = EARLIER ( 'Table (2)'[Position] )
    ),
    [PTS.Avg]
)
POS.Avg Column 2 = 
CALCULATE (
    AVERAGE ( 'Table (2)'[PTS.Avg] ),
    FILTER (
        'Table (2)',
        'Table (2)'[Position] = EARLIER ( 'Table (2)'[Position] )
    )
)

column.JPG

 

Measure:

POS.Avg Measure 1 = 
AVERAGEX (
    FILTER (
        ALLSELECTED ( 'Table (2)' ),
        'Table (2)'[Position] = MAX ( 'Table (2)'[Position] )
    ),
    [PTS.Avg]
)
POS.Avg Measure 2 = 
CALCULATE (
    AVERAGE ( 'Table (2)'[PTS.Avg] ),
    FILTER (
        ALLSELECTED ( 'Table (2)' ),
        'Table (2)'[Position] = MAX ( 'Table (2)'[Position] )
    )
)
POS.Avg Measure 3 = 
CALCULATE (
    AVERAGE ( 'Table (2)'[PTS.Avg] ),
    ALLEXCEPT ( 'Table (2)', 'Table (2)'[Position] )
)

measure.JPG

 

 

Best regards

Icey

 

If this post helps, then consider Accepting it as the solution to help other members find it faster.

View solution in original post

Super User IV
Super User IV

Just add a DAX calculated column with this formula

 

Avg Column = CALCULATE(AVERAGE(Table[Pts.Avg]), ALLEXCEPT(Table, Table[Position]))

 

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


View solution in original post

Helper I
Helper I

I ended up doing the following:

 

GroupedRows = Table.Group(#"Reordered Columns1", {"Position"}, {{"POS.AVG", each List.Average([PTS.Avg]), type nullable number}}),
#"MergedQueries" = Table.NestedJoin(#"Reordered Columns1",{"Position"},GroupedRows,{"Attribute"},"DvP",JoinKind.Inner)

 

Those 2 with a calc column worked great.

 

View solution in original post

Post Prodigy
Post Prodigy

You posted this question in the Power Query forum, but I think the solution is best done in DAX. 

 

You should investigate the ALL and ALLSELECTED DAX functions to tag along in your AVG function.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

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