Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Establishing Quartiles for Summarized Data

Hi, I am attempting to take a table of data grouped using the SUMMARIZE function and assign quartiles in an added column.  I'm using a table variable to store the table of summarized data, but the variables I'm creating to establish the Percentile values are throwing an error:

 

"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

 

This makes sense, but I can't seem to refer to the specific column as AutoComplete isn't suggesting anything, and typing the column name appears to be syntactically incorrect.  Here is my code:

 

 

TASK_DISTRIBUTION = 
var vTaskCount = 
    ADDCOLUMNS(
        SUMMARIZE(
            ALL_TASKS,
            ALL_TASKS[MnemonicDomain]
        ),
    "TaskCountByClient", CALCULATE(COUNTROWS(ALL_TASKS))
    )
    
var P75 =CALCULATE(PERCENTILEX.INC( vTaskCount, [TaskCountByClient], 0.75 ))
var P50 =CALCULATE(PERCENTILEX.INC( vTaskCount, [TaskCountByClient], 0.50 ))
var P25 =CALCULATE(PERCENTILEX.INC( vTaskCount, [TaskCountByClient], 0.25 ))

var vReturnColumns =
ADDCOLUMNS(
    vTaskCount, "Percentile", SWITCH(
        TRUE(),
        vTaskCount >= P75, ">= 75th Percentile",
        vTaskCount >= P50, ">= 50th Percentile",
        vTaskCount >= P25, ">= 25th Percentile",
        "<25th Percentile" )
)

RETURN
    vReturnColumns

 

 

 

I think I am probably making a fairly basic mistake... I am just looking for a table that produces something like the following:

 

MnemonicDomainTaskCountByClientPercentile
ABC_DE99>= 75th Percentile
FGH_IJ95>= 75th Percentile
KLM_NO52>= 50th Percentile
PQR_ST2< 25th Percentile

 

Thanks in advance!

  • Are you trying to create a measure or a calculated table? If it's a calculated table, then I think you just need to replace "vTaskCount >= PXX" with "[TaskCountByClient] >= PXX".

     

    As a side note, IntelliSense/AutoComplete isn't always right. It occasionally gives me angry red underlines when my DAX is fine, but this mostly happens in uncommon situations.

2 Replies

  • Are you trying to create a measure or a calculated table? If it's a calculated table, then I think you just need to replace "vTaskCount >= PXX" with "[TaskCountByClient] >= PXX".

     

    As a side note, IntelliSense/AutoComplete isn't always right. It occasionally gives me angry red underlines when my DAX is fine, but this mostly happens in uncommon situations.

    • Anonymous's avatar
      Anonymous
      Not applicable

      Thank you!  This fixed the issue.