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.

     

     

     

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

Vvelarde

 

Thanks for the help & response.

 

The VAR P80 measure returns a value.  The Return Calculation measure returned a blank.  When applying that measure to a PivotTable, i received the error: "MdxScript(Model) (6,35) Calculation error in measure 'Table1'[VAR P80]: The Percentile value should be in the range 1/(N+1)...N/(N+1) inclusive, where N is the number od data values.

 

Should any of the two measures you suggested be a calculated column instead of measure?  I'm confused b/c the measures should seem to work.  

 

Kindly,

James

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