cancel
Showing results for
Did you mean:

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

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

2 ACCEPTED SOLUTIONS
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
Blog
Super User

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
Blog
7 REPLIES 7
Helper II

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

Super User

@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
Blog
Helper II

Super User

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
Blog
Helper II

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

Helper II

brilliant!!!!! thanks!

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
Blog

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors