Forum Discussion
Measure based on grouped calculation without summarize
I need to calculate measure based on groups. And tricky part is that I cannot use summarize as I need to have date filter and measure should dynamically change when I change a filter.
For example for data:
| day | PurchaseId | UserId |
| 1 | 1 | 1 |
| 1 | 2 | 1 |
| 1 | 3 | 2 |
| 1 | 4 | 2 |
| 1 | 5 | 3 |
| 2 | 6 | 1 |
| 2 | 7 | 2 |
| 2 | 8 | 3 |
| 2 | 9 | 4 |
| 3 | 10 | 1 |
| 3 | 11 | 4 |
| 3 | 12 | 4 |
| 3 | 13 | 4 |
| 3 | 14 | 4 |
| 4 | 15 | 1 |
| 4 | 16 | 2 |
| 4 | 17 | 3 |
When filter is all days - i want to see
| UserId | Amount of purchases |
| 1 | 5 |
| 2 | 4 |
| 3 | 3 |
| 4 | 5 |
And when filter is 3-4 day
| UserId | Amount of purchases |
| 1 | 2 |
| 2 | 1 |
| 3 | 1 |
| 4 | 4 |
EDITED
Moreover I want to have buckets afterwards, based on this amount (for exmple, 1-2 purchases, 3-5 purcahes) and operate with them
| Bucket | Amount of users |
| 1-2 purchases | 0 |
| 3-5 purcahes | 4 |
filter 3-4 day
| Bucket | Amount of users |
| 1-2 purchases | 3 |
| 3-5 purcahes | 1 |
Any ideas, how can I do this?
hi, kzorina
You could use this formula to add a new measure
Measure 2 = var _table=SUMMARIZE(GENERATE(buckets,'Table'), [Tag],[From],[To],[UserId],"Amount of users",CALCULATE(COUNTA('Table'[PurchaseId]))) return COUNTAX(FILTER(_table,[Amount of users]>=[From]&&[Amount of users]<=[To]),[UserId])Then drag Tag field from buckets table and the measure into a visual
Best Regards,
Lin
4 Replies
hi, kzorina
You could try this way as below:
Step1:
You need to define buckets table.
Step2:
Use this formula to create the measure
Measure = var _amount= CALCULATE(COUNTA('Table'[PurchaseId])) return CALCULATE(MAX(buckets[Tag]),FILTER(buckets,_amount>=buckets[From]&&_amount<=buckets[To]))Result:
Best Regards,
Lin
Thanks, v-lili6-msft for a quick answer
Can I somehow achive it in a way so I can operate with bucket as fixed value. for example, calculate amount of users in some bucket?
hi, kzorina
You could use this formula to add a new measure
Measure 2 = var _table=SUMMARIZE(GENERATE(buckets,'Table'), [Tag],[From],[To],[UserId],"Amount of users",CALCULATE(COUNTA('Table'[PurchaseId]))) return COUNTAX(FILTER(_table,[Amount of users]>=[From]&&[Amount of users]<=[To]),[UserId])Then drag Tag field from buckets table and the measure into a visual
Best Regards,
Lin