cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
WirelessBrains Frequent Visitor
Frequent Visitor

Make a Column that averages numbers from another Column Based on Relationship

So I have a column that is formatted as follows

 

Booking_Table

 

Order # | Vendor  |  Days Difference

 

1               Vnd1                  3

2               Vnd1                  6

3               Vnd1                -1

4               Vnd2                  4

5               Vnd2                  5

6               Vnd3                 -2

 

And a second table that is compoased of just distinct vendors

 

Vendor_Average

 

Vendor | 

 

Vnd1

Vnd2

Vnd3

 

I want to add a second column to this table that is the AVERAGE of a vendors days diference from table one, as shown below

 

 

Vendor_Average

 

Vendor | Average Days

 

Vnd1                2.1

Vnd2               -1.3

Vnd3                4.1

 

I have tried the following along with several other things but I have not been able to figure out what to do exactly. It seems like a simple concept but I cannot figure out how to execute it. Any assistance in the correct direction would be apprecieated.

 

Note, this is the DAX expression to create the table, I am attempting to add the column through the table creation expression.

 

Vendor_Average = SUMMARIZE(Booking_Table, Booking_Table[Vendor], "AvgNumDay", AVERAGEX(FILTER(Booking_Table, Booking_Table[Vendor] = RELATED(Vendor_Average(Vendor))

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-yulgu-msft Super Contributor
Super Contributor

Re: Make a Column that averages numbers from another Column Based on Relationship

Hi @WirelessBrains,

 

Please modify your DAX formula as below:

Vendor_Average =
SUMMARIZE (
    Booking_Table,
    Booking_Table[Vendor],
    "Average Days", AVERAGE ( Booking_Table[Days Difference] )
)

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yulgu-msft Super Contributor
Super Contributor

Re: Make a Column that averages numbers from another Column Based on Relationship

Hi @WirelessBrains,

 

Please modify your DAX formula as below:

Vendor_Average =
SUMMARIZE (
    Booking_Table,
    Booking_Table[Vendor],
    "Average Days", AVERAGE ( Booking_Table[Days Difference] )
)

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

WirelessBrains Frequent Visitor
Frequent Visitor

Re: Make a Column that averages numbers from another Column Based on Relationship

Worked perfectly! Glad to see that I was not that far off, thanks.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 6 members 4,456 guests
Please welcome our newest community members: