crackednut
New Member
- Joined
- Feb 12, 2015
- Messages
- 5
hello BI experts, have started to play around with power pivot and power BI over the last few days.
Particular case here is to create bins (measure or column, not sure!) that reflect the weekly deciles for data which is given at a daily level. There is a lot of literature written on percentilex formula but my limited knowledge is unable to make progress with my data set. End objective is to perform a deep dive into various trends that emerge out of segmenting the column = "name". data is collated at atomic level at "name" with parent hierarchies such as "category", "pub_type", "pub_name", "source", "lang" and so on.
the task I would like to perform is to provide an analysis on the deciles bins based on one metric ("imp") and therefore go upward to find the various trends emerging in the parent hierarchies. however, for now I am unable to even bridge to the first hurdle asI am finding it difficult to select the right cut within the percentilex function & create the necessary bins in weekly filter.
My hunch tells me I have to create a disconnected table that stores all the weekly percentiles which needs to be looked up to classify the records. Am not sure about the right syntax for this.
The masked data I am working on can be found here -> Decile buckets sample_v04.xlsx
desired result is in the table below. this has been created manually for week 10.
objective is to do this for any given week selected via filter.
Thanks again for all the help. Do excuse me if there's a delay in response.
Particular case here is to create bins (measure or column, not sure!) that reflect the weekly deciles for data which is given at a daily level. There is a lot of literature written on percentilex formula but my limited knowledge is unable to make progress with my data set. End objective is to perform a deep dive into various trends that emerge out of segmenting the column = "name". data is collated at atomic level at "name" with parent hierarchies such as "category", "pub_type", "pub_name", "source", "lang" and so on.
the task I would like to perform is to provide an analysis on the deciles bins based on one metric ("imp") and therefore go upward to find the various trends emerging in the parent hierarchies. however, for now I am unable to even bridge to the first hurdle asI am finding it difficult to select the right cut within the percentilex function & create the necessary bins in weekly filter.
My hunch tells me I have to create a disconnected table that stores all the weekly percentiles which needs to be looked up to classify the records. Am not sure about the right syntax for this.
The masked data I am working on can be found here -> Decile buckets sample_v04.xlsx
desired result is in the table below. this has been created manually for week 10.
objective is to do this for any given week selected via filter.
| bucket | Sum of imp | % of weekly wt | % of weekly vv | Count of name |
| b01 | 932673814 | 7.90% | 6.68% | 18 |
| b02 | 934656935 | 6.67% | 6.12% | 22 |
| b03 | 961283535 | 4.78% | 4.52% | 32 |
| b04 | 947067972 | 6.91% | 8.27% | 42 |
| b05 | 960823086 | 11.67% | 10.11% | 68 |
| b06 | 949331469 | 11.61% | 10.39% | 121 |
| b07 | 950857956 | 8.45% | 9.02% | 237 |
| b08 | 948768489 | 9.18% | 11.32% | 467 |
| b09 | 949697407 | 6.86% | 9.31% | 1184 |
| b10 | 948376578 | 25.98% | 24.26% | 39025 |
| Grand Total | 9483537241 | 100.00% | 100.00% | 41216 |
Thanks again for all the help. Do excuse me if there's a delay in response.
