Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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.
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.
@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
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |