Forum Discussion

dsl98's avatar
dsl98
Frequent Visitor
2 years ago
Solved

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:

January4
February4
March5
April4
May4
Jun5
Jul4
Aug4
Sep5
Oct4
Nov4
Dec5
Q113
Q226
Q339
Q452
H126
H252
Jan YTD4
Feb YTD8
Mar YTD13
Apr YTD17
May YTD21
Jun YTD26
Jul YTD30
Aug YTD34
Sep YTD39
Oct YTD43
Nov YTD47
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!

  • Anonymous's avatar
    Anonymous
    2 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

  • Anonymous's avatar
    Anonymous
    Not 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