cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jmg889 Visitor
Visitor

Assign accounts to Household

Let's say I have the following data:

- 10 Total Account numbers (row records), have one product type of Phone or Internet

- 5 Unique Households (say they're called "Household 1"..."Household 5")

- Each Household (HH) has 2 Accounts

- A HH can be classified as "Double Phone" = 2x Phones,  "Double Internet" = 2x Internet, or "Mix" = 1x of each 

 

So, how do I assign that HH classification to each account?

 

And ideally, that new field (assignment) could be used for grouping purposes and I could study that group.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Anonymous
Not applicable

Re: Assign accounts to Household

HH = 
VAR varPhone = CALCULATE(COUNTROWS('Data'),FILTER(ALLEXCEPT('Data','Data'[Household]),'Data'[Product] = "Phone"))
VAR varInternet = CALCULATE(COUNTROWS('Data'),FILTER(ALLEXCEPT('Data','Data'[Household]),'Data'[Product] = "Internet"))
RETURN
    IF( varPhone > 0, IF(varInternet >0, "Mix", "Phone"),"Internet")

You can try the above. And add this code as a calculated column to your table.  It will give you the following result.

 

2019-07-17_22h05_34.png

View solution in original post

1 REPLY 1
Anonymous
Not applicable

Re: Assign accounts to Household

HH = 
VAR varPhone = CALCULATE(COUNTROWS('Data'),FILTER(ALLEXCEPT('Data','Data'[Household]),'Data'[Product] = "Phone"))
VAR varInternet = CALCULATE(COUNTROWS('Data'),FILTER(ALLEXCEPT('Data','Data'[Household]),'Data'[Product] = "Internet"))
RETURN
    IF( varPhone > 0, IF(varInternet >0, "Mix", "Phone"),"Internet")

You can try the above. And add this code as a calculated column to your table.  It will give you the following result.

 

2019-07-17_22h05_34.png

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)