cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Surya9
Helper I
Helper I

Getting different measure value while using PERCENTILEX

I used this measure to get the values 
Percentile Q1 =
var a = SUMMARIZE(tblKPIDetails,tblKPIDetails[Agent name],"Act",[Actual])
var c = PERCENTILEX.INC(a,[Act],0.25)
Return c

and I got the expected output when i am using the card visual but when i use this measure in for the table it's not workin there
Surya9_0-1620408537365.png

the output should be 83.33 for each row.

1 ACCEPTED SOLUTION
OwenAuger
Super User I
Super User I

Hi @Surya9 

On each row of the table, the measure is evaluated with that row's "Agent name" filter applied.

 

To get the result you want, you can use CALCULATETABLE with the ALLSELECTED() modifier to restore the "overall" filter context of the table, for the purpose of variable a.

I would also suggest using ADDCOLUMNS to create the extended column, and using VALUES instead of SUMMARIZE since you are summarizing by a column in the same table.

 

The resulting measure would be:

 

Percentile Q1 =
VAR a =
    CALCULATETABLE (
        ADDCOLUMNS ( VALUES ( tblKPIDetails[Agent name] ), "Act", [Actual] ),
        ALLSELECTED ()
    )
VAR c =
    PERCENTILEX.INC ( a, [Act], 0.25 )
RETURN
    c

 

 Does this give the expected result?


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

2 REPLIES 2
v-deddai1-msft
Community Support
Community Support

Hi @Surya9 ,

 

Please try to use the following measure:

 

Percentile Q1 =
var a = SUMMARIZE(ALL(tblKPIDetails),tblKPIDetails[Agent name],"Act",[Actual])
var c = PERCENTILEX.INC(a,[Act],0.25)
Return c

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

 
OwenAuger
Super User I
Super User I

Hi @Surya9 

On each row of the table, the measure is evaluated with that row's "Agent name" filter applied.

 

To get the result you want, you can use CALCULATETABLE with the ALLSELECTED() modifier to restore the "overall" filter context of the table, for the purpose of variable a.

I would also suggest using ADDCOLUMNS to create the extended column, and using VALUES instead of SUMMARIZE since you are summarizing by a column in the same table.

 

The resulting measure would be:

 

Percentile Q1 =
VAR a =
    CALCULATETABLE (
        ADDCOLUMNS ( VALUES ( tblKPIDetails[Agent name] ), "Act", [Actual] ),
        ALLSELECTED ()
    )
VAR c =
    PERCENTILEX.INC ( a, [Act], 0.25 )
RETURN
    c

 

 Does this give the expected result?


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors