creating percentile bins at weekly level for daily data

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.

bucketSum of imp% of weekly wt% of weekly vvCount of name
b019326738147.90%6.68%18
b029346569356.67%6.12%22
b039612835354.78%4.52%32
b049470679726.91%8.27%42
b0596082308611.67%10.11%68
b0694933146911.61%10.39%121
b079508579568.45%9.02%237
b089487684899.18%11.32%467
b099496974076.86%9.31%1184
b1094837657825.98%24.26%39025
Grand Total9483537241100.00%100.00%41216


Thanks again for all the help. Do excuse me if there's a delay in response.
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Watch MrExcel Video

Forum statistics

Threads
1,095,246
Messages
5,443,321
Members
405,227
Latest member
Ankit Jain

This Week's Hot Topics

Top