Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
GregVia
Frequent Visitor

Measure to average fare excluding the duplicates

Hello,

 

I have a table like this below where some rows are duplicates (due to reason so I cannot delete them), I woud like a measure that permit to calculate the avereage Fare for each resource taking fare only once for project. 

 

AAA shuold be (100+250)/2 and not (100+250+100)/3

 

 

ResourceFareProject
AAA100A111
BBB200A111
CCC350B222
DDD135C333
AAA250C333
BBB350B222
CCC450C333
DDD450B222
CCC450C333
DDD450B222
DDD300D444
AAA100A111

 

this is the table of correct averege result:

 

 Avarege
AAA175
BBB275
CCC400
DDD295

 

I don't know which is the best solution to solve this problem if measure or trough new table.

 

Could yu please help me?

 

Thank

 

GV

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@GregVia , try measure like


averageX(summarize(Table,Table[Project],Table[Resource], Table[Fare]), [Fare])

 

or

 

averageX(summarize(Table,Table[Project],Table[Resource], "_1", max(Table[Fare])), [_1])

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

@GregVia , try measure like


averageX(summarize(Table,Table[Project],Table[Resource], Table[Fare]), [Fare])

 

or

 

averageX(summarize(Table,Table[Project],Table[Resource], "_1", max(Table[Fare])), [_1])

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.