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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.