Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

AVERAGEIFS in Power BI

Hi there

 

I'm trying to replicate AVERAGEIFS from excel in Power BI by looking at a list of sales, which have a tier attached to them based on value, and I'd like to get the averages for each tier (see below):

 

 

I have a table of sales in Power BI with the tiers as above, what DAX is required to create a column next to the tiers to calculate the averages across the entire sales columns for each of the tiers?

 

Thank You

Dhushy

Need Help

4 Replies

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Icon for Community Champion rankCommunity Champion
      8 years ago

      Anonymous

       

      Average Value =
CALCULATE ( AVERAGE ( Table1[Sales] ), ALLEXCEPT ( Table1, Table1[Tier] ) )
      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        This seems to give an average over all the tiers when I use it, thus giving me the same value for all Tiers - any idea why?

         

        Using Average(Table[Sales]) and then using Tiers as Rows worked however, I think I still need to do a lot of learning as to why that is the case but it's solved the problem at hand.

         

        Thanks for your help!

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Thanks! Using Average by itself over the Sales column and combining it with Tiers (as Row values) seems to have done th trick.