topic Distinct Count by comparing two summarized tables in DAX Commands and Tips

Distinct Count by comparing two summarized tables

enoch99 — Thu, 10 Aug 2023 09:22:32 GMT

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.

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

Re: Distinct Count by comparing two summarized tables

nandukrishnavs — Thu, 10 Aug 2023 11:43:19 GMT

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

Re: Distinct Count by comparing two summarized tables

enoch99 — Thu, 10 Aug 2023 11:53:06 GMT

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

Re: Distinct Count by comparing two summarized tables

nandukrishnavs — Thu, 10 Aug 2023 12:39:10 GMT

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

Re: Distinct Count by comparing two summarized tables

enoch99 — Thu, 10 Aug 2023 15:47:04 GMT

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