Forum Discussion
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:
| MnemonicDomain | TaskCountByClient | Percentile |
| ABC_DE | 99 | >= 75th Percentile |
| FGH_IJ | 95 | >= 75th Percentile |
| KLM_NO | 52 | >= 50th Percentile |
| PQR_ST | 2 | < 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
- AlexisOlson
Super User
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.
- AnonymousNot applicable
Thank you! This fixed the issue.