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.
Hello,
I am not sure how to do this so I would love some advice from the all of the experts in this community. I have a table containing five years of data for approximately 250 organizations, roughly 1,250 total observations. Here's what it looks like:
I have a few other variables in the table; but, I am hoping to calculate the quantiles (or, percentile bins, 1-5) for the variable "RatioNetAssetsUnrestricted" for each year, determine what bin each organization falls based on its RatioNetAssetsUnrestricted for that year, and then return that bin number in a calculated column. Here's what I am trying to achieve:
This seems similar to a previous question, here, but I can't determine how to customize that solution to answer my question. Any help will be much appreciated. Thank you!
Solved! Go to Solution.
How about something like:
Bin= SWITCH(TRUE,
Table1[Ratio] <= PERCENTILE.INC(Table1[Ratio], 0.25), 1,
Table1[Ratio] <= PERCENTILE.INC(Table1[Ratio], 0.50), 2,
Table1[Ratio] <= PERCENTILE.INC(Table1[Ratio], 0.75), 3,
4)
?
Please try the following
1. Create a new table called Band with coulmn names - Position, MinimumValue, MaximumValue
2. The rows of this table will be
1, 0, 0.30
2, 0.31,0.50
3,0.51,0.70
4, 0.71, 0.90
5, 0.91, 1.00
3. In the fact table define a column Bin
=CALCULATE (
VALUES ( Band[Position] ),
FILTER (
Band,
FactTable[RatioNetAssetsUnrestricted] >= Band[MinimumValue]
&&FactTable[RatioNetAssetsUnrestricted] <= Band[MaximumValue]
)
)
This will work.
If you have any issues let me know.
If you find the above steps solved your problem, please accept as solution and also give Kudos.
Thanks for the suggestion, scottsen. But, it won't work in my situation because I can't rely on static bins. My data is linked to a SQL table, which will change because not all of the 2015 data has been input. There are also changes that occur from time-to-time for data that's already been entered. The solution needs to be dynamic.
How about something like:
Bin= SWITCH(TRUE,
Table1[Ratio] <= PERCENTILE.INC(Table1[Ratio], 0.25), 1,
Table1[Ratio] <= PERCENTILE.INC(Table1[Ratio], 0.50), 2,
Table1[Ratio] <= PERCENTILE.INC(Table1[Ratio], 0.75), 3,
4)
?
This is great! Thanks for the quick and easy solution.
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 |
---|---|
112 | |
99 | |
82 | |
70 | |
63 |
User | Count |
---|---|
145 | |
111 | |
104 | |
84 | |
64 |