Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
103 | |
101 | |
87 | |
73 | |
67 |
User | Count |
---|---|
116 | |
109 | |
94 | |
79 | |
72 |