creating percentile bins at week level for daily data

[Anonymous]

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. 

The masked data I am working on can be found here -> https://drive.google.com/open?id=1vjkXcmmOWEnUDaUmtFn6XiJC58o0kCCu

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. 

[technolog · Accepted solution]

Firstly, you're trying to create bins based on the weekly deciles for daily data. Given that you want to analyze the data at a weekly level, you'll need to first aggregate your data at that level.

You can create a new column in your data model that extracts the week number from your date. This can be done using the WEEKNUM function in DAX. For example: WeekNumber = WEEKNUM([YourDateColumn]). This will give you the week number for each of your daily data points.

Once you have the week number, you can then aggregate your data by week. You can create a new table or modify your existing one to sum up the "imp" metric for each week.

Now, for the decile bins, you'll use the PERCENTILEX function. This function will allow you to determine the value at a particular percentile within a given set of values.

For creating the bins, you'd do something like this:

For the first bin (b01), you'd find the value at the 10th percentile.
For the second bin (b02), you'd find the value between the 10th and 20th percentile.
And so on, until you reach the 100th percentile for the last bin (b10).
The DAX for the first bin might look something like this:
b01 = CALCULATE(PERCENTILEX.INC(ALL(Table[imp]), Table[imp], 0.1))

You'd repeat this for each bin, just changing the percentile value.

Once you have your bins, you can then create measures to calculate the sum of "imp", the percentage of weekly weight, percentage of weekly vv, and count of "name" for each bin.

For the sum of "imp" for each bin, you'd use a simple SUM function. For the percentages, you'd divide the sum of "imp" for each bin by the grand total of "imp" for the week. And for the count of "name", you'd use the COUNT function.

Lastly, to make this dynamic based on the week selected in a filter, you'd use the SELECTEDVALUE function to get the currently selected week and then filter your calculations based on that week.