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
Icon for Super User rankSuper 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)