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 slici...
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)
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.

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