Forum Discussion

Mente73's avatar
Mente73
Frequent Visitor
1 year ago
Solved

Help with calculations based on slicer selection

Hello everyone, new to Power Bi and forum. I appeciate all your help in advance.   I am creating a matrix and want to include a slicer with week, month, Qtr and 1-2H (done through a prm) In the m...
Need Help
  • sevenhills's avatar
    sevenhills
    1 year ago

    Check this video for similar solution:

    https://www.youtube.com/watch?v=obfYqap4hnQ

     

    Coming back to your .pbix, I quickly fix and it is very slow. You may have to do as per the video or look on internet for better performance.

    Optional measure for debug:

    Measure debug = var _sv = Max( Prm_Date[Prm_Date] )

RETURN _sv & " : " & 
switch ( _sv, 
  "Date", SELECTEDVALUE(DimDate[Date]),
  "Week", SELECTEDVALUE(DimDate[WeekofYear]),
  "Month", SELECTEDVALUE(DimDate[Month]),
  "Quarter", SELECTEDVALUE(DimDate[Quarter]),
  "Half Year", SELECTEDVALUE(DimDate[Half Year])
  
  , "??")

     

    Actives PW = 
 var _sv = Max( Prm_Date[Prm_Date] )
 RETURN  
CALCULATE(DISTINCTCOUNT('Actives New'[CustomerID])
            ,FILTER(ALL(DimDate), DimDate[Year]=SELECTEDVALUE(DimDate[Year]) &&
                        switch ( _sv, 
                        "Date", DimDate[Date] = SELECTEDVALUE(DimDate[Date]),
                        "Week", DimDate[WeekofYear]=(SELECTEDVALUE(DimDate[WeekofYear])-1) ,
                        "Month", DimDate[Month] = SELECTEDVALUE(DimDate[Month]),
                        "Quarter", DimDate[Quarter] = SELECTEDVALUE(DimDate[Quarter]),
                        "Half Year", DimDate[Half Year]= SELECTEDVALUE(DimDate[Half Year]) 
                        , "??") 
            )
)

     

    Retention % = If ( not ISBLANK([Actives PW]) && [Actives PW] <> 0, 
 ([Active]-[FTD's])/[Actives PW]
)

     

    I just added Measure debug and Actives PW for checking. you can remove those in the below visuals:

    sample for half year:

    sample for quarter:

     

    Hope it helps!

sevenhills's avatar
sevenhills
Icon for Super User rankSuper User
1 year ago

First, you can get rid of infinity using 

Retention % = 

IFERROR(([Active]-[FTD's])/[Actives PW], BLANK() )

 

Second, your formula for getting the Actives PW uses WeekofYear and when you select other than the week, it obviously not work correctly and which in turn results to infinity.

 

Actives PW = CALCULATE(DISTINCTCOUNT('Actives New'[CustomerID])
,FILTER(ALL(DimDate),
DimDate[Year]=SELECTEDVALUE(DimDate[Year]) &&
DimDate[WeekofYear]=SELECTEDVALUE(DimDate[WeekofYear])-1))
 
 
You are getting value for SELECTEDVALUE(DimDate[Year]) from the slicer. I am not sure what values you used for the other slicer? can you clarify?
 
Also, could you you create a dummy measure and add to your table and see what value you are getting for "SELECTEDVALUE(DimDate[WeekofYear]"?

  • Mente73's avatar
    Mente73
    Frequent Visitor
    1 year ago

    Hello Sevenhills.

     

    Thank you for checking on it,

     

    I am attaching a few more screenshot that may help you to answer your questions. Like I said, I an a newbie in PBI and get lost too. 

     

    This is the DimDate I havethis is the prm parameteresThis is the other slicer (Year)This is what I get from dummy measure

     

    Again, thank you for helping me out

      • Mente73's avatar
        Mente73
        Frequent Visitor
        1 year ago

        Watched both and the 2nd is closer to what I am trying to achieve however I got lost on the VAR for the periods as they are different to the one I need.

        I appreciate if you can help me to make it work.  I can start from scratch.

         

        Thanks

        M

  • Mente73's avatar
    Mente73
    Frequent Visitor
    1 year ago

    Watched both videos and 2nd relates more however i got lost on the VAR for the periods since they differ from mine.

     

    If you think you can help me out, I can start from scratch to make it happen

     

    Regards

     

    M