Forum Discussion

Jkaelin's avatar
Jkaelin
Icon for Resolver I rankResolver I
9 years ago
Solved

First Post! Calculate Average Sales Within Percentile

  Good afternoon,   I'm trying to calculate a dynamic DAX measure to provide the average Net Profit for the Top 20% of Sales by Date.     A formula that does NOT work (returns a blank value):  =...
Need Help
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    Jkaelin

     

    Hi, try with this:

     

    Measure =
    VAR P80=PERCENTILE.EXC(Table1[Sales],0.8)
    RETURN
    CALCULATE(AVERAGE(Table1[netProfit]),FILTER(Table1,Table1[Sales]>=P80))

  • Vvelarde's avatar
    Vvelarde
    9 years ago

    Jkaelin

     

    hi, is just one measure. put together in the creation of the measure. don't split in 2.

     

    Let me know if works.

     

     

     

Vvelarde's avatar
Vvelarde
Icon for Community Champion rankCommunity Champion
9 years ago

Jkaelin

 

hi, is just one measure. put together in the creation of the measure. don't split in 2.

 

Let me know if works.

 

 

 

Jkaelin's avatar
Jkaelin
Icon for Resolver I rankResolver I
9 years ago

Vvelarde

 

That method seems to work great.  You were a great help.  The final formula was:  

 

Return:=CALCULATE(Average(Table1[Net Profit]),Filter(Table1,Table3[Sales]>=PERCENTILE.EXC(Table1[Sales],0.8)))

 

Thank you,

James