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
Jo_Chrq
Helper I
Helper I

AVERAGE IF at least one line contains ...

Hi all,

 

I'm struggling to find the right way to do the following calculation:

 

I have a Sales table which contains ticket numbers. Each ticket has several lines for each item sold.

Items have categories.

I would like to find the aveage quantity sold for tickets that have sold at least one item with category A.

 

TicketArticle categoryArticleQuantityAmount (€)
111AArticle xxx1100 €
111BArticle xxx210 €
111CArticle xxx120 €
112BArticle xxx115 €
112DArticle xxx112 €
113BArticle xxx15 €
113AArticle xxx1150 €
113CArticle xxx123 €
113DArticle xxx224 €
113FArticle xxx115 €
114EArticle xxx319 €
114BArticle xxx110 €
114BArticle xxx29 €
115AArticle xxx1120 €
115EArticle xxx12 €
115FArticle xxx315 €

 

In this example, it would be :

Average = (ticket 111 = 4) + (ticket 113 = 6) + (ticket 115 = 5) / 3 (tickets) = 15 / 3 = 5

 

May thanks in advance for your help.

Best

Jonathan

1 ACCEPTED SOLUTION
AntonioHR_
Frequent Visitor

Hello,
I'm sure this is not the best solution but will work.

Create a table only with tickets with "A" category:
table2 =

CALCULATETABLE(DISTINCT(Table[Ticket]); Table[Article category] = "A")
Link it with your original table by ticket and create a new column in the original table:
Column = RELATED(Table2[ticket])
Then you can do the measure to calculate the average:
MEASURE = CALCULATE(
SUM(
Table[Quantity])/DISTINCTCOUNT('Table2'[Ticket]);
Table[Column] <> BLANK()
)



View solution in original post

2 REPLIES 2
Jo_Chrq
Helper I
Helper I

Hi @AntonioHR_ 

I don't if it's the best way but it's definitely a way!

It works just fine, thansk for your help.

Best

AntonioHR_
Frequent Visitor

Hello,
I'm sure this is not the best solution but will work.

Create a table only with tickets with "A" category:
table2 =

CALCULATETABLE(DISTINCT(Table[Ticket]); Table[Article category] = "A")
Link it with your original table by ticket and create a new column in the original table:
Column = RELATED(Table2[ticket])
Then you can do the measure to calculate the average:
MEASURE = CALCULATE(
SUM(
Table[Quantity])/DISTINCTCOUNT('Table2'[Ticket]);
Table[Column] <> BLANK()
)



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.