I have to get the actual FB Usage and FB Cycles based on Month Start date and user ID.

[Balaraju]

Hi All,

I have a data below, I have to get the actual FB Usage and FB Cycles based on Month Start date and user ID. 

Eg: A1 user is used FB on 01-jan-21 1 time, 2nd jan 2 times, 3rd jan 3 times, 4th jan 10 times. I want to create DAX like

it should aggregate based on usage and if total usage comes under 10 it should show as 1 to 10 Bucket Range . If the total usage is 25 it should come under 20 to 30 Bucket Range and so on. 

NOTE: The data is on Daily basis, but visual  need to be seen on  Monthly basis.

Raw data

Raw Data
User Id	Month Start date	FB Usage
A1	01-Jan-21	1
A1	02-Jan-21	2
A1	03-Jan-21	3
A1	04-Jan-21	10
A1	05-Jan-21	5
A2	01-Jan-21	2
A2	02-Jan-21	1
A2	03-Jan-21	5
A2	04-Jan-21	7

[v-eqin-msft · Accepted solution]

Hi @Balaraju ,

Please try the following formula:

Sum FB Usage = CALCULATE(SUM('Table'[FB Usage]),ALLEXCEPT('Table','Table'[User Id]))

Bucket Range = SWITCH(TRUE(),
[Sum FB Usage]>0 && [Sum FB Usage]<=10, "0-10 Cycles",
[Sum FB Usage]>10 && [Sum FB Usage]<=15, "10-15 Cycles",
[Sum FB Usage]>15 && [Sum FB Usage]<=20, "15-20 Cycles",
[Sum FB Usage]>20 && [Sum FB Usage]<=30, "20-30 Cycles")

Output:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Balaraju , you can create new columns

cnt = countx(filter(table, [User Id] = earlier([User Id]) ) , [FB Usage]),

Bucket = Switch( True() ,

[cnt] <10, "   0 -10 cycle",

[cnt] <=15, "  10 -15 cycle",

[cnt] <=20, "  16 -20 cycle",

[cnt] <=30, "  21 -30 cycle",

"30 Onward Cycle"

)

if you need bucket on a measure, refer

Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k