Count of customer enrolled in other store & Transacting in this store

[arvindarvind24]

Hi All,

   Need your help on getting Count of customer enrolled in other store & Transacting in this store,
Please find the attached link for the sample File.

code	Total Enrolled	Enrolled in store(Count)	Enrolled in store	members enrolled in other store & Transacting in this store(Count)	members enrolled in other store & Transacting in this store	enrolled in other store & Transacting in this store(Count)	enrolled in other store & Transacting in this store
10				1	3000
14	2	1	1000	1	2000	1	3000
1054	1					1	2000
Total	3	1	1000	2	5000	2	5000

Link: https://drive.google.com/file/d/19crjTP6tSvXzSvvP8wCk6WoD9_ps7mdJ/view?usp=sharing

@amitchauhan @bvanderwatt @ray_ux @tamerj1 @SpartaBI @v-chenyue-msft  @mangaus1111 

[v-jialluo-msft · Accepted solution]

^{Hi @arvindarvind24 ,}

^{Please follow these steps:}

^{(1) Create a new measure}

SALES =
CALCULATE (
    SUM ( Sales[netamt] ),
    FILTER (
        ALL ( 'Sales' ),
        RELATED ( 'Customer'[admsite_code] ) = MAX ( 'Site'[admsite_code] )
            && NOT ( [admsite_code] & [redmobilenumber] )
                IN SUMMARIZE (
                    INTERSECT (
                        SUMMARIZE (
                            'Customer',
                            [admsite_code],
                            [mobile],
                            "1", [admsite_code] & [mobile]
                        ),
                        SUMMARIZE (
                            'Sales',
                            [admsite_code],
                            [redmobilenumber],
                            "1", [admsite_code] & [redmobilenumber]
                        )
                    ),
                    [1]
                )
    )
)

^{(2)Final output}

^{Best Regards,}

^{Gallen Luo}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}

[arvindarvind24]

Hi @v-jialluo-msft 

 Output is correct but how abt grand total is not corrent.

[arvindarvind24]

@v-jialluo-msft Thank you🙏

[daXtreme]

This is not clear. Please give a good example in 'slow motion.' It's really hard to understand how your table calculates, especially if one is new to this. Please always remember that you do know the model and data. We don't.

[arvindarvind24]

[arvindarvind24]

Hi @daXtreme 

Thank you for your replay,The model which i have shared is CRM data,
where custome is created in A1 store but transation is done in A2 store.

Where i have calcuated Customer is created A1 and billing is happed in the same store and also
Customer is created in A3 and billing is done in A1 store.
but mot able to calcuate where customer is created in A1 and billing in other store.