Pacing %

[Anonymous]

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

[danextian]

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]

@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]

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

[Anonymous · Accepted solution]

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