Forum Discussion

enoch99's avatar
enoch99
Icon for Helper I rankHelper I
3 years ago
Solved

Distinct Count by comparing two summarized tables

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's avatar
    nandukrishnavs
    3 years ago

    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

4 Replies

    • enoch99's avatar
      enoch99
      Icon for Helper I rankHelper I
      3 years ago

      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's avatar
        nandukrishnavs
        Icon for Community Champion rankCommunity Champion
        3 years ago

        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