Lookup / Unique Count Problem in DAX

Hello Anonymous !

For this sort of calculation, I would suggest writing a measure that:

1. Groups the 'Activity Report' table by ActivityInfo[Minutes to Complete]
2. Iterates over this grouped table, multiplying ActivityInfo[Minutes to Complete] by the row count of 'Activity Report'.

To do this, I would create two measures:

Activity Count =
COUNTROWS ( 'Activity Report' )

Total Minutes =
SUMX (
    SUMMARIZE( 'Activity Report', ActivityInfo[Minutes to Complete] ),
    ActivityInfo[Minutes to Complete]
        * [Activity Count]
)

You can then create a PivotTable with 'ActivityInfo'[Activity Code] on the rows, with the Total Minutes measure displayed.

This measure should work correctly with any other filters you might want to apply.

I've knocked this together in a copy of your Excel file, with a 2nd PivotTable displaying the two measures (attached).

Is that the sort of thing you were looking for?

Regards,

Owen