Forum Discussion

Icon for Resolver I rank

Resolver 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): =CALCULATE(Average(Table1[Net Profit]),FILTER(Table1,Table1[Sales]>=[80th Percentile])))

80th Percentile:=PERCENTILE.EXC(Table1[Sales],0.8)

*If I made any forum posts errors, please let me know.

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
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.

4 Replies

Vvelarde
Community Champion
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))
- Jkaelin
  Resolver 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
    Community 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.

Related content