Sum values of column A only if column B is unique

[Moehimby]

Hey all, I'm stuck and need your expertise, appreciate any help in advance.

I have a table organized by ZIP Codes, problem is it has duplicates because some ZIPs are shared between 2 or more customers. 

Is there a way to have the measure only SUM houses for each unique ZIP code? Example of data :

ZIP Code	Houses	Customer
90210	25	John
90211	100	Rick
90211	100	Sandy
90212	15	John

So as you'd see in the above ZIP 90211 will return 200 as the sum of houses, when it should in fact be 100.

I've tried approaches like 

CALCULATE( SUM ( 'table'[Houses]) , VALUES( 'table'[ZIP Code] ) but to no avail, it returns the same over stated values every time.... also tried exchanging VALUES for DISCTINCT but same results again.

[Anonymous]

Hello,hnguy71 and Ashish_Mathur,thanks for your concern about this issue.

Your answers are excellent!
And I would like to share some additional solutions below.

Hi,@Moehimby.I am glad to help you.

After my testing, the advice given by hnguy71 is helpful. Defining a virtual table in measure can create a new computing environment for measure without being affected by the VISUAL that the current measure is in. I think using virtual tables is really a good choice. 

The code of the measure creates a new table where each zip code corresponds to an adjusted number of houses. The adjusted number of houses is calculated by dividing the total number of houses by the number of unique clients. This method can help calculate the distribution of houses under each zip code.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian

[Ashish_Mathur]

Hi,

Just drag the ZIP code to the Table visual and write this measure

Measure = min('table'[Houses])

[hnguy71 · Accepted solution]

Hi @Moehimby ,

That's possible but that means only Rick or Sandy would get the 100. Is there a business logic to break ties? If all you're interested in the total, here's a version that may work for you:

SUMX(
    SUMMARIZE (
        'Table',
        'Table'[ZIP Code],
        "@AdjustHomes", DIVIDE(SUM('Table'[Houses]), DISTINCTCOUNT('Table'[Customer]))
    ),
    [@AdjustHomes]
)