Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Pacing %

Hi All,

 

I'm stuck at a hurdle and need immediate help.

I have created a DAX to calculate pacing % and it is working fine till the time i'm using dates in line chart and using filters for slicing and dicing the data.

 


But when I try to create a matrix to show pacing % on various cuts like Business Unit/SVP/RVP it is giving me exactly same value for my measure. I'm not sure what is wrong here. 

Even using other measures i.e Cumulative SQO$ and Cumulative SQO Pacing I'm getting the same numbers for any BU I'm selecting. 

 

 

 

I've attached the file for reference and need urgent help.


Measures used:

  • Cumulative_SQO$_Actuals =
VAR MaxDate = MAX(F_SQO_FLM_TARGETS[FULL_DATE])
VAR Today = [Today]
VAR Result =
    CALCULATE(
        SUM(F_SQO_FLM_TARGETS[SQO_DOLLARS_ACTUALS_DAILY]),
        FILTER(
            ALLSELECTED(F_SQO_FLM_TARGETS),
            F_SQO_FLM_TARGETS[FULL_DATE] <= MaxDate
        )
    )
RETURN
IF(Today>=Max(F_SQO_FLM_TARGETS[FULL_DATE]),Result,BLANK())
 
  • Cumulative_SQO$_Targets =
VAR MaxDate = MAX(F_SQO_FLM_TARGETS[FULL_DATE])
RETURN
    CALCULATE(
        SUM(F_SQO_FLM_TARGETS[SQO_DOLLARS_TARGET_DAILY]),
        FILTER(
            ALLSELECTED(F_SQO_FLM_TARGETS),
            F_SQO_FLM_TARGETS[FULL_DATE] <= MaxDate
        )
    )

 

  • Weekly SQO$ Pacing % = DIVIDE([Cumulative_SQO$_Actuals],[Cumulative_SQO$_Targets],0)

 

Pbix File Location : https://drive.google.com/file/d/1VxioEEHSw9RqMkicXCGmUW8ehMExY4Jg/view?usp=sharing

Regards,
Himanshu

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    danextian Thanks for trying here but I got the desired output after doing few tweeks in my measures.

    The desired result is exactly the same numbers which are there on the Pipegen pacing page but I wanted the Pacing % to get filter on some specific dimensions like BU/SVP/RVP.

    Below is the correct and desired result:


    You actually pointed me to the right direction where I was using ALLSELECTED instead of ALL operation and that somehow corrected my measures.

    The measures I used which got the desired result:

    • New_SQO_Actuals =
    VAR _Today = [Today]
    VAR _SUM1 =
        CALCULATE (
            SUM ( F_SQO_FLM_TARGETS[SQO_ACTUALS_DAILY] ),
            FILTER (
                ALL ( F_SQO_FLM_TARGETS[Last Day of Week_F] ),
                [Last Day of Week_F] <= MAX ( F_SQO_FLM_TARGETS[Last Day of Week_F] )
            )
        )
    VAR _SUM2 =
        CALCULATE (
            SUM ( F_SQO_FLM_TARGETS[SQO_ACTUALS_DAILY] ),
            FILTER ( ALL ( F_SQO_FLM_TARGETS ), F_SQO_FLM_TARGETS[FULL_DATE] <= _Today )
        )
    RETURN
        IF ( _Today >= MAX ( F_SQO_FLM_TARGETS[Last Day of Week_F] ), _SUM1, BLANK () )

    • New_SQO_Targets =
    VAR _Today = [Today]
    VAR _SUM1 =
        CALCULATE (
            SUM ( F_SQO_FLM_TARGETS[SQO_TARGET_DAILY] ),
            FILTER (
                ALL ( F_SQO_FLM_TARGETS[Last Day of Week_F] ),
                [Last Day of Week_F] <= MAX ( F_SQO_FLM_TARGETS[Last Day of Week_F] )
            )
        )
    VAR _SUM2 =
        CALCULATE (
            SUM ( F_SQO_FLM_TARGETS[SQO_TARGET_DAILY] ),
            FILTER ( ALL ( F_SQO_FLM_TARGETS ), F_SQO_FLM_TARGETS[FULL_DATE] <= _Today )
        )
    RETURN
        IF ( _Today >= MAX ( F_SQO_FLM_TARGETS[Last Day of Week_F] ), _SUM1, BLANK () )

    • New_SQO_Pacing = DIVIDE([New_SQO_Actuals],[New_SQO_Targets],0)

4 Replies

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi Anonymous 

    You are applying the result of your calculations to all visible rows of F_SQO_FLM_TARGETS and all dimensions are coming from this table. You already have a separate dates table so why not use it?

    See the changes below:

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      danextian I have already tried this and this is still not giving the correct output. If you'll see the Pipegen pacing page the pacing % for week starting 11/09 or 11/16 and so on is different from the numbers you have shown above. 

      Check the tooltip and you'll see the difference.

       



      The numbers on the pacing page are correct.

      • danextian's avatar
        danextian
        Super User
        1 year ago

        It will not help to just  say it is not giving the correct output. Please provide the expected result and the reasoning behind.