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