Distinct Count by comparing two summarized tables

[enoch99]

Hi everyone,

I am new to DAX and need your support in solving this problem. 

I have two Excel files. One contains yearly target planned for different activities while the second contains monthly reports of reached. I want to count those activities that are lagging behind. 

1. 

2. 

I tried to do smoething like this but I got lost in the middle:

var maxMonth = MAX('Reached'[month])
var target_table =SUMMARIZE('Target', 'Target'[Activity], "Monthly Target", CALCULATE ( DIVIDE(SUM( 'Target'[Target] ),12,0)))
var beneficiary_table = SUMMARIZE('Reached', 'Reached'[Activity], "Monthly Reached", CALCULATE ( DIVIDE(SUM( 'Reached'[Reached] ),maxMonth,0)))

What I want is a measure that distinct counts activities that are lagging behind ie. Monthly Target > Monthly Reached.

I would very much appreacite your help.

Thank you

[nandukrishnavs]

@enoch99 based on above dataset, what is the expected result. Can you mention that as well?

[enoch99]

@nandukrishnavsI want a table like this:

I think I can compare the Target and Reached columns after that to count the number of activities lagging behind.

Thanks

[nandukrishnavs · Accepted solution]

@enoch99 You can create a calculated column in Target table

Reached =
SUMX (
    FILTER ( Reached, [Activity] = EARLIEST ( Target[Activity] ) ),
    [Reached]
)

If you need a measure, then try the below measure

Lagging = 
VAR _target =
    ADDCOLUMNS (
        Target,
        "@Reached",
            SUMX (
                FILTER ( Reached, [Activity] = EARLIER ( Target[Activity] ) ),
                [Reached]
            )
    )
VAR _result =
    COUNTX ( FILTER ( _target, [@Reached] < [Target] ), [Activity] )
RETURN
    _result

[enoch99]

@nandukrishnavs  Thank you for your quick response. This is what I was looking for.