Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Good morning
I have a table with two column number
- one column is the value that I will use for calculate the percentile value
- one column is the fiscal week
I would like to trend the percentile value in a chart with below condition
In the axis I have FW numbers where
FW1 is the percentile calc for the value that we have FW=1
FW2 is the percentile calc for the value that we have FW<=2
FW3 is the percentile calc for the value that we have FW<=3...
How I can create this? I need to create a separated table?
I noted the PERCENTILEX.INC but I'm not sure about the expression that I need to use...
Thanks
Regards
Riccardo
Do you need to display multiple formulas on a single chart? In order to help you better, could you please provide some sample data and the expected results based on these sample data? That would be very helpful.
My suggestions so far are as follows for your reference:
First, calculate the percentile value corresponding to FW=1, FW<=2, and FW<=3, and then use the following formula:
Measure =
SWITCH(TRUE(),
SELECTEDVALUE(YOURTABLENAME[YOURTABLECOLUMN]) = "FW=1"), [FW=1 formula],
SELECTEDVALUE(YOURTABLENAME[YOURTABLECOLUMN]) = "FW<=2"), [FW<=2 formula],
SELECTEDVALUE(YOURTABLENAME[YOURTABLECOLUMN]) = "FW<=3"), [FW<=3 formula])
Best Regards,
Yulia Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for your reply
below an example
FWTime
Column A [FW] | Column B [Time] | ||||
1 | 437.90 | Percentile 0.5 FW1 | 289.0885127 | Percentile when FW<=1 | |
1 | 320.53 | Percentile 0.5 FW2 | 232.5888831 | Percentile when FW<=2 | |
1 | 257.65 | Percentile 0.5 FW3 | 143.3801389 | Percentile when FW<=3 | |
1 | 249.50 | Percentile 0.5 FW4 | 139.7399248 | Percentile when FW<=4 | |
2 | 215.68 | ||||
2 | 143.48 | ||||
2 | 143.28 | ||||
2 | 139.95 | ||||
3 | 139.53 | ||||
3 | 130.59 | ||||
3 | 125.82 | ||||
3 | 122.80 | ||||
4 | 117.86 | ||||
4 | 111.55 | ||||
4 | 110.81 | ||||
4 | 110.77 |
For calculate of the percentile I used the below formula
Percentile 0.5 FW1 =PERCENTILE.INC(B2:B5;0.5)
Percentile 0.5 FW2 =PERCENTILE.INC(B2:B9;0.5)
Percentile 0.5 FW3 =PERCENTILE.INC(B2:B13;0.5)
Percentile 0.5 FW4 =PERCENTILE.INC(B2:B17;0.5)
I used in PowerBI the formula
User | Count |
---|---|
98 | |
90 | |
78 | |
71 | |
64 |
User | Count |
---|---|
112 | |
96 | |
95 | |
67 | |
65 |