Forum Discussion

nicoenz's avatar
nicoenz
Icon for Helper III rankHelper III
2 years ago
Solved

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  
A1……100   
B1……150   
C1……0   
D1……0   
E1……0   
F1……23   
G1……15   
A2……21   
B2……8   
C2……9   
D2……15   
E2……23   
F2……33   
G2……45   
A3……60 Percentile 33
B3……48 20.46 
C3……84   
D3……65   
E3……33   
F3……29   
G3……12   
A4……35   
B4……66   
C4……79   
D4……98   
E4……8   
F4……46   
G4……32   

 

(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

 

 

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

Need Help
  • OwenAuger's avatar
    OwenAuger
    2 years ago

    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

  • OwenAuger's avatar
    OwenAuger
    2 years ago

    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

7 Replies

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    2 years ago

    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

  • nicoenz's avatar
    nicoenz
    Icon for Helper III rankHelper III
    2 years ago

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

    • OwenAuger's avatar
      OwenAuger
      Icon for Super User rankSuper User
      2 years ago

      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.