-1

I have written this DAX query to calculate the 90th and 75th percentiles of my dataset. However, I am encountering an issue where the 90th percentile measure for RevPAR is displaying as blank, while the other measures are showing values. Similarly, for the 75th percentile, all measures are appearing as blanks.

that's how i calculate 90th percentile revpar measure--

90th Percentile RevPAR = 
PERCENTILEX.INC(
    FILTER(Sheet1, Sheet1[Bedrooms] = SELECTEDVALUE(Sheet1[Bedrooms]) && Sheet1[Custom_area] = SELECTEDVALUE(Sheet1[Custom_area]) && Sheet1[Active Nights] = SELECTEDVALUE(Sheet1[Active Nights]) && Sheet1[Beachfront] = SELECTEDVALUE(Sheet1[Beachfront]) && Sheet1[Comp Name] = SELECTEDVALUE(Sheet1[Comp Name]) && Sheet1[Days_sold] = SELECTEDVALUE(Sheet1[Days_sold]) && Sheet1[Dynamic Pricing] = SELECTEDVALUE(Sheet1[Dynamic Pricing]) && Sheet1[Professionally Managed] = SELECTEDVALUE(Sheet1[Professionally Managed]) && Sheet1[Revenue] = SELECTEDVALUE(Sheet1[Revenue]) && Sheet1[translated_Address] = SELECTEDVALUE(Sheet1[translated_Address])  ),
    Sheet1[Revpar],
    0.9
)

this comes out as a blank, same way i create 75th percentile measure which is blank as well.

90th percentile revenue measure -

90th Percentile Revenue = 
CALCULATE(
    AVERAGE(Sheet1[Revenue]),
   FILTER(Sheet1, Sheet1[Revpar] >= [90th Percentile RevPAR])
)

this is showing some values

    75th Percentile Revenue = 
    CALCULATE(
        AVERAGE(Sheet1[Revenue]),
       FILTER(Sheet1, Sheet1[Revpar] >= [75th Percentile RevPAR] && Sheet1[Revpar] < [90th Percentile RevPAR])

)

this also comes as blank.enter image description here

Can somemone please assist me on this.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
shubham tiwari
  • 71
  • 4

1 Answers1

0

PERCENTILEX.INC function format is PERCENTILEX.INC(<table>, <expression>;, k)

You missed to give Table as first argument.

90th Percentile RevPAR = 
PERCENTILEX.INC(Sheet1,
    FILTER(Sheet1, Sheet1[Bedrooms] = SELECTEDVALUE(Sheet1[Bedrooms]) && Sheet1[Custom_area] = SELECTEDVALUE(Sheet1[Custom_area]) && Sheet1[Active Nights] = SELECTEDVALUE(Sheet1[Active Nights]) && Sheet1[Beachfront] = SELECTEDVALUE(Sheet1[Beachfront]) && Sheet1[Comp Name] = SELECTEDVALUE(Sheet1[Comp Name]) && Sheet1[Days_sold] = SELECTEDVALUE(Sheet1[Days_sold]) && Sheet1[Dynamic Pricing] = SELECTEDVALUE(Sheet1[Dynamic Pricing]) && Sheet1[Professionally Managed] = SELECTEDVALUE(Sheet1[Professionally Managed]) && Sheet1[Revenue] = SELECTEDVALUE(Sheet1[Revenue]) && Sheet1[translated_Address] = SELECTEDVALUE(Sheet1[translated_Address])  ),
    Sheet1[Revpar],
    0.9
)
Ashok Anumula
  • 1,338
  • 1
  • 4
  • 9