Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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

Need Help
Tutorial Requests

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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