Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Anonymous
Not applicable

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!

1 ACCEPTED SOLUTION
AlexisOlson
Super User
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.

View solution in original post

2 REPLIES 2
AlexisOlson
Super User
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.

Anonymous
Not applicable

Thank you!  This fixed the issue.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.