Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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
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
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.
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
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
brilliant!!!!! thanks!
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
57 |
User | Count |
---|---|
188 | |
111 | |
105 | |
78 | |
71 |