cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Floris Visitor
Visitor

Average with specific conditions

Hi there,

 

I am just starting to discover PowerBI and it looks promising but I am stuck with a specific problem. I'd like to make a trend analysis for several different regions over four years and with multiple products.

Calculating the averages for all the products and seeing how they develop over time is not very difficult, however, I would like to select ONLY the regions which have provided me with information in all four years. 

 

So: How can I make a select the regions that have given me data over all four years and for each specific product?

 

I currently only have a table with all data, including the four years, all the regions and all different products and related prices.

 

I am looking forward to hearing from you, if any additional information is required, please let me know.

 

Best regards,

 

Floris

2 REPLIES 2
Super User
Super User

Re: Average with specific conditions

Several different ways to accomplish this, report, page or visual level filters, using AVERAGEX with an appropriate FILTER clause. If you want this to be dynamic, that will take a little more work but could be accomplished using a temporary table and IN most likely. But, that would need sample data to be specific. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Community Support Team
Community Support Team

Re: Average with specific conditions

Hi @Floris

It is not clear of your information, before you provide more details, i would like to give two methods as below.

with this measure, i can calcuate how many years each region give the information

measure-region = CALCULATE(DISTINCTCOUNT(Sheet6[year]),ALLEXCEPT(Sheet6,Sheet6[regions]))

with this measure,   i can calcuate how many years each product give the information

Measure-each product = CALCULATE(DISTINCTCOUNT(Sheet6[year]),ALLEXCEPT(Sheet6,Sheet6[products]))

3.png

 

For next steps, please let me know what exactly you want.

 

Best Regards

Maggie