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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
nicoenz
Helper II
Helper II

Percentile 33 based on a summarized table

Hi everyone, 

I have a table with production data by product, region and many other columns

Table 1       
ProductCustomerRegionother columns in tableProduction  
A1100   
B1150   
C10   
D10   
E10   
F123   
G115   
A221   
B28   
C29   
D215   
E223   
F233   
G245   
A360 Percentile 33
B348 20.46 
C384   
D365   
E333   
F329   
G312   
A435   
B466   
C479   
D498   
E48   
F446   
G432   

 

(using the percentile.inc measure i get the 20.46 value)

 

 

But... 

What I want is to select the region in slicer and calculate the Percentile 33 of the sum of the production values by product.
In this case the value should be 130.46

 

nicoenz_2-1698053335627.png

 

no matter what i try, i always get the 20 so I'm going crazy. Is there a way to do it?

Regards and thanks, 

Nico

2 ACCEPTED SOLUTIONS
OwenAuger
Super User
Super User

Hi @nicoenz 

Use PERCENTILEX.INC like this:

Production Percentile 33 by Product = 
PERCENTILEX.INC (
    VALUES ( Production[Product] ),
    CALCULATE ( SUM ( Production[Production] ) ),
    0.33
)

 

Also, if you create a measure for Production Sum (which I would recommend) e.g.

Production Sum =
SUM ( Production[Production] )

then you can write:

 

Production Percentile 33 by Product = 
PERCENTILEX.INC (
    VALUES ( Production[Product] ),
    [Production Sum],
    0.33
)

 

Does this work for you?

 

Regards


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

Hi again @nicoenz 

Apologies, I was caught up yesterday.

 

You could reformulate slightly and create a measure like this:

Production below 33rd Percentile = 
VAR Percentile33 =
    CALCULATE (
        PERCENTILEX.INC ( VALUES ( Production[Product] ), [Production Sum], 0.33 ),
        ALLSELECTED ()
    )
VAR ProductionBelowPercentile33 =
    SUMX (
        VALUES ( Production[Product] ),
        VAR Prod = [Production Sum]
        RETURN
            IF ( Prod < Percentile33, Prod )
        )
RETURN
    ProductionBelowPercentile33

Does this work for you?

 

Regards


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

7 REPLIES 7
nicoenz
Helper II
Helper II

Hi @OwenAuger ,
How can i now retrieve the summarized table with all values below that percentile value? 🙂

@nicoenz Glad the percentile measure is working for you 🙂


Did you want to create a visual similar to that in your original post, but just showing values below the 33rd percentile? 
i.e. Products G & E in this example.


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

@OwenAuger :  YES!!!!!! please!

Hi again @nicoenz 

Apologies, I was caught up yesterday.

 

You could reformulate slightly and create a measure like this:

Production below 33rd Percentile = 
VAR Percentile33 =
    CALCULATE (
        PERCENTILEX.INC ( VALUES ( Production[Product] ), [Production Sum], 0.33 ),
        ALLSELECTED ()
    )
VAR ProductionBelowPercentile33 =
    SUMX (
        VALUES ( Production[Product] ),
        VAR Prod = [Production Sum]
        RETURN
            IF ( Prod < Percentile33, Prod )
        )
RETURN
    ProductionBelowPercentile33

Does this work for you?

 

Regards


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Hi @OwenAuger ,

absolutely no need to apologize!!!!! really appreciate your support.

i wil try it today and accept it as solution if it works well

thanks again

nicoenz
Helper II
Helper II

brilliant!!!!! thanks!

 

OwenAuger
Super User
Super User

Hi @nicoenz 

Use PERCENTILEX.INC like this:

Production Percentile 33 by Product = 
PERCENTILEX.INC (
    VALUES ( Production[Product] ),
    CALCULATE ( SUM ( Production[Production] ) ),
    0.33
)

 

Also, if you create a measure for Production Sum (which I would recommend) e.g.

Production Sum =
SUM ( Production[Production] )

then you can write:

 

Production Percentile 33 by Product = 
PERCENTILEX.INC (
    VALUES ( Production[Product] ),
    [Production Sum],
    0.33
)

 

Does this work for you?

 

Regards


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.