Forum Discussion
Dynamic Weekly Run Rate Measure based on 2 slicers (Quarter and Month), and different criteria
Hi, I have searched online for a while but no avail. Assuming I have 2 slicers (slicer 1 has Q1 Q2 Q3 and Q4, and slicer 2 has Months - January, February...) from file 1, that has one to many relationship linked to my source sales file (file 2). How do I make a dynamic measure formula so that everytime I select the slicer it will auto calculate cumulatively, with below criterias:
| January | 4 |
| February | 4 |
| March | 5 |
| April | 4 |
| May | 4 |
| Jun | 5 |
| Jul | 4 |
| Aug | 4 |
| Sep | 5 |
| Oct | 4 |
| Nov | 4 |
| Dec | 5 |
| Q1 | 13 |
| Q2 | 26 |
| Q3 | 39 |
| Q4 | 52 |
| H1 | 26 |
| H2 | 52 |
| Jan YTD | 4 |
| Feb YTD | 8 |
| Mar YTD | 13 |
| Apr YTD | 17 |
| May YTD | 21 |
| Jun YTD | 26 |
| Jul YTD | 30 |
| Aug YTD | 34 |
| Sep YTD | 39 |
| Oct YTD | 43 |
| Nov YTD | 47 |
| Dec YTD (default) | 52 |
basically the expected formula is
WRR = Divide (numerator is filter the sales list, denominator is selected period AND OR quarter as per above)
eg. if I have a line of sales made in April at 1000, i would expect the formula to auto calculate when I selected respective slicer:
select April in Period - 1000 divide by 4
select Q2 in Quarter (ie. same as selecting April, May and June in Period) - 1000 divide by 13
select Q1 and Q2 in Quarter - 1000 divide by 26
select April, May in Period - 1000 divide by 8
and so on
I have tried different variations of formula but can only get either one of the results, I have linked the many to one relationships properly. Thanks!
- Anonymous2 years ago
Hi dsl98,
It sounds like you want to use slicer to apply selector effect instead of filter effects.
For this scenario, I'd like to suggest you create a disconnected table as source of slicer, then you can add variables in the Dax expression to calculate the current total volumes and selected range volumes.
SELECTEDVALUE function - DAX | Microsoft Learn
Regards,
Xiaoxin Sheng
1 Reply
- AnonymousNot applicable
Hi dsl98,
It sounds like you want to use slicer to apply selector effect instead of filter effects.
For this scenario, I'd like to suggest you create a disconnected table as source of slicer, then you can add variables in the Dax expression to calculate the current total volumes and selected range volumes.
SELECTEDVALUE function - DAX | Microsoft Learn
Regards,
Xiaoxin Sheng