Average with specific conditions

[Anonymous]

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

[v-juanli-msft]

Hi @Anonymous

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]))

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

Best Regards

Maggie

[Greg_Deckler]

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