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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PasiNiinikoski
Frequent Visitor

Calculating average with values more than x

I have this very simplified data sheet:

 

WorkerNameDateProductsMade
John2019/12/0140
John2019/12/0150
John2019/12/0590
John2019/12/0570
John2019/12/07110
John2019/12/15150
John2019/12/1870
John2019/12/21115

 

My workers work several shifts per day. And John is not my only worker 🙂

Lets say I would like to know how many products my workers make on the average per day. That is easy.

But lets say I would like know that information but concerning only those days that they make more than 100 products: average per day per worker.

 

Any help? In my query or in Power BI?

 

Thanks in advance,

 

Pasi Niinikoski

3 REPLIES 3
az38
Community Champion
Community Champion

Hi @PasiNiinikoski 

try a measure

Measure = CALCULATE(AVERAGE(Query[ProductsMade]);ALLEXCEPT(Query;Query[WorkerName]);Query[ProductsMade]>100)

 

do not hesitate to give a kudo to useful posts and mark solutions as solution


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Hi @az38 and thanks,

 

will try that but I must first somehow calculate the sum of the ProductsMade by worker per day and then apply that measure. 

 

-Pasi

@PasiNiinikoski 

ProductsMade = CALCULATE(SUM('Table'[Products]);ALLEXCEPT('Table';'Table'[WorkerName];'Table'[Date]))

 

do not hesitate to give a kudo to useful posts and mark solutions as solution


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors