Percentile 33 based on a summarized table

[nicoenz]

Hi everyone, 

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

Table 1
Product	Customer	Region	other columns in table	Production
A	1	…	…	100
B	1	…	…	150
C	1	…	…	0
D	1	…	…	0
E	1	…	…	0
F	1	…	…	23
G	1	…	…	15
A	2	…	…	21
B	2	…	…	8
C	2	…	…	9
D	2	…	…	15
E	2	…	…	23
F	2	…	…	33
G	2	…	…	45
A	3	…	…	60		Percentile 33
B	3	…	…	48		20.46
C	3	…	…	84
D	3	…	…	65
E	3	…	…	33
F	3	…	…	29
G	3	…	…	12
A	4	…	…	35
B	4	…	…	66
C	4	…	…	79
D	4	…	…	98
E	4	…	…	8
F	4	…	…	46
G	4	…	…	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

[OwenAuger · Accepted solution]

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]

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

[OwenAuger]

@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.

[nicoenz]

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

[OwenAuger · Accepted solution]

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

[nicoenz]

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]

brilliant!!!!! thanks!

[OwenAuger · Accepted solution]

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