better way to get dynamic bucket data with Date Hierarchy

[Anonymous]

Hi,

Could you help to advise if there is a way in Power BI can make following scenario into Visual, and be able to drill down/drill up by day, by week? If not possible, please help to advise the question in "PS" section in the end. THANKS!

Sample Data 

Scenario:

There are raw tables in a folder in excel format contain over 20,000,000 rows * 8 columns, (expanding 1million rows per week).

Each raw excel table contains following information: (sample data)

Now, we need to create visual to monitor the Distinct Cout of Query or Page under each volume level of "Clicks" by Day, AND by Week. (Volume Level is additional info, on the bais of clicks, e.g. 1-1000, 1001-10000, etc)

If in excel, taking "Distinct count of Page" as sample, it will be done by

1. adding WeekNum, Month, Year as additional columns in raw file.

2. Create pivot table by day, create another pivot table by week, Append a column for "Volume Level"

3. Create another 2 pivot tables on the basis of step 2, to get the the "Distinct Count of Page" by day, by week.

4. Create separate diagrams on the basis of each pivot table in step 3.

Distinct Count of Pages in each Volume Level By Week

Distinct Count of Pages in each Volume Level By Day

If there is a better way in Power BI?

PS:

I was thinking to load the raw files into power BI and add custom columns append WeekNum, Month, Year, then create a "pivot" table sum all data at Week Num level, then append "Volume Level" column.

Then I realized that will make no possible if user want to drill up/down by Date hierarchy, since in the new created "Pivot" power BI table, there is no "Date" information as all data has been summarized to WeekNum level.

But if there is no better way, it's more important for us to view the by week summarzied Volume Level,

then, please help to advise how to make it in Power BI, which means how to summarzie the by Day data into by Week in power BI table?

Thanks.

Please advise.

Thank you.

H

[amitchandak]

@Anonymous , You have a date in your data. With that you can create week in power bi

A date table with week columns

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Number = WEEKNUM([Date],2)
Week = if('Date'[Week Number]<10,'Date'[Year]*10 & 'Date'[Week Number],'Date'[Year]&'Date'[Week Number])
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
Week name = [Week Start date] & " to "& [Week End date]

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Then you can week rank to compare WOW , or use any of the week columns from date table to group the data week wise

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8

[Anonymous]

Thanks for reply @amitchandak , but my question is not about how to give weeknum.

It's about, how to get the distinct count of page by Volume Level by Week.

For example, for the following item, it's "By Day" volume level will be 1-1000, but when it summarzied by Week, the level will be 1001-5000.

So, my question is how to make it being processed by Day AND by Week automatcially.

Which means, in a visual, if it's by Day hierarchy, there is 1 distinct count page in 1-1000 in 2020-1-2, and 1 distinct page in 1-1000 in 2020-1-3.

When drill up to "WeekNum", there is 1 distinct page in 1001-5000.

[amitchandak]

@Anonymous , for that you need to create an independent bucket.

which will have range.

Assume you have measure

week cnt = distinctcount(Table[Page])

You have create new measures for all the measures you want to use with bucket (assume bucket new independent table)

countx(filter(values(Table[Week), [Week cnt] >= min(bucket[Start]) && [Week cnt] >=[Week Cnt]),[Week Cnt])

Refer this video, where I explained this

https://youtu.be/CuczXPj0N-k

other examples are

https://www.daxpatterns.com/dynamic-segmentation/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

[Anonymous]

Thanks @amitchandak , it's good to know that.

Seems the question is still there.

Let's say I've set bucket to 1-300, 301-1000.

As you can see, the point is that now, there is "by day" table only, with "Country" breakdown.

Then, if a "Query", let's say "Search Term 1" has

100 clicks in day 1 in USA,

100 clicks in day 2 in USA,

100 clicks in day 1 in UK,

300 clicks in day 2 in UK,

Then how to make it automatcially bucketed to 301-1000 clicks when drill down to "Week" level no matter which country it is?

Then can drill up to 1-300 bucket in Day 1, 301-1000 bucket in Day 2 when drill up to "Day" level?

If that's impossible, maybe the best way is to create another Power BI table aggregate all into "Week" level first. - Which as OP said, not possible for date hierarchy drill up / down.

Thanks for your valuable inputs.

[amitchandak]

@Anonymous , You need week either in table or date table 

This part code when we use will create a bucket after grouping data at the week level

countx(filter(values(Table[Week),

Group the data at week and join to independent bucket table

countx(filter(values(Table[Week), [Week cnt] >= min(bucket[Start]) && [Week cnt] >=[Week Cnt]),[Week Cnt])

I will try with your data and update you

[Anonymous]

Hi @amitchandak , after applying the new measure, seems there is an error.

Appreciate if you can help.

[Anonymous]

@amitchandakthanks for the further explanation!

One addition question, is it possible set dynamic bucket when drill up/down to Day/Week?

E.g.

If it's at Day level in Hierarchy, "Big volume" bucket = 100-1000, "Mid"= 50-100

If it's at Week level, "Big volume" = 10000-50000, "Mid" = 500-1000"

Thanks again!

[amitchandak · Accepted solution]

@Anonymous , see if the attached file can help . Bucket you can edit as per need

If the file can help, How to edit the bucket I created https://www.youtube.com/watch?v=5nE7YGT72kU

[Anonymous]

Hi @amitchandak ,

If you don't mind, may I ask another 2 questions:

1. How to get Sum of Clicks under each Bucket by week? Let's say there are 2 pages have 1001 clicks in Week2, then totally in bucket 1001-5000, there are 2002 clicks in the bucket  in week 2.

I tried to drag & drop the Clicks in Value of the table, but failed.

Update: Please ignore above question, I tried to use SUMX and get it. Thanks.

Question: 2. is it possible set unlimited max value?

for example, give "Start" as 10000, then all volume over 10000 will be the "Big Volume" bucket?

If that's another big topic, Please let me know, I will open a new post.

Thanks!

[Anonymous]

That's awesome, sir, Appreciate your time and great help! @amitchandak