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 Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

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

Top Solution Authors
Top Kudoed Authors