Calculation won't roll up across granularities properly

[bendarr]

Hi all!

I am working on porting over all our company reporting from Tableau to PowerBI.  I have run into an issue with one of the migrations as the report does on the fly calculations against what essentialy amount to cartesian products of some large sets of data.  I have worked my way through most of them and at this point am stuck on the last calc which I have spent the better half of the day beating my head against to try and figure out.  The basic concept is there is a set of data for site locations (RTFN_CAMPAIGNOPPORTUNITYANALYSIS_STUDYSITES), and a set of data with lead locations (RTFN_CAMPAIGNOPPORTUNITYANALYSIS_LEADINFO).  The lead location data is at a granularity below the lead, so from the perspective of my needs, that table has many duplicate values for each lead.  Through options in the report, the site location data can be filtered down using a Study parameter, and the leads can be filtered down through a Date Range paramter.  Finally the site locations and lead locations data sets are linked together using a both filtering relationship via a hardcoded join key of 1.

I built a measure with the below logic where I attempted to dedupe and limit the data through table variables summarizing only the columns I needed.  Then build a cartesian product of the two table variables, and finally do a distance calculation on each combo of lead and site created, grabing the minimum distance in miles to use for my logic.  Finally, I return a count of leads that have at least one site within 30 miles of them.  Here is the measure logic:

TOT_IN_MILE_RANGE =

    VAR PI_DIV180 = DIVIDE(PI(), 180)

    VAR LeadInfo =

        SUMMARIZE(

            RTFN_CAMPAIGNOPPORTUNITYANALYSIS_LEADINFO,

            [LEAD_SOURCE_INFO_ID],

            [HAS_NEW_OPP_FLAG],

            [LEAD_AGE_GROUP],

            [LEAD_LATITUDE],

            [LEAD_LONGITUDE]

        )

    VAR StudySites =

        SUMMARIZE(

            RTFN_CAMPAIGNOPPORTUNITYANALYSIS_STUDYSITES,

            [STUDY_ID],

            [SITE_ID],

            [SITE_LATITUDE],

            [SITE_LONGITUDE]

        )

    VAR Cartesian = CROSSJOIN(LeadInfo, StudySites)

    VAR CartesianMod =

        SUMMARIZE(

            Cartesian,

            [STUDY_ID],

            [HAS_NEW_OPP_FLAG],

            [LEAD_AGE_GROUP],

            "MILES_DISTANCE",

            MINX(

                Cartesian,

                ACOS(SIN([LEAD_LATITUDE]*PI_DIV180)*SIN([SITE_LATITUDE]*PI_DIV180)+COS([LEAD_LATITUDE]*PI_DIV180)*COS([SITE_LATITUDE]*PI_DIV180)*COS(([SITE_LONGITUDE]*PI_DIV180)-([LEAD_LONGITUDE]*PI_DIV180)))*3959

            )

        )

    RETURN

    SUMX(CartesianMod, IF([MILES_DISTANCE] <= 30, 1, 0) )

The TOT_NOT_IN_MILE_RANGE measure is just my leads - TOT_IN_MILE_RANGE.

The result is below, and it didn't match up to the original report

To troubleshoot, I dropped my unique lead identifier in, and mocked up a measure to return the minimum mile distance for each lead.  This is where things got odd in that the roll up totals didn't match what the individual lead values indicated.  You can see in the below screenshot for the 18-19 age group that the totals show 5 leads didnt have a site in range and 1 did.  However, when you add up the granular values, its actually 4 leads that didnt have a site in range and 2 did, with these totals being the right answer.

At this point I have exhausted my abilities to figure out why this difference exists.  I have tried changing granularity of the data in the table variables, or attempting to remove filters on the outputs and am not having any success.  Any help at this point would be greatly appreciated.

Thanks!

[bendarr · Accepted solution]

I was able to resolve the issue using the solution posted here

https://community.fabric.microsoft.com/t5/Desktop/Measure-totals-not-summing-correctly/td-p/2216682