count of distinct values across multiple columns with in the same table.

[Anonymous]

I have a table with sales and multiple owners (owner 1, owner 2, owner 3) the goal is to create a measure that calculates the average sales per owner. so the eqaution is sales / count of owners.

what i am having difficulty with is getting the count of owners. the business need is to only have distinct values from all 3 columns. From a querying perspective, it would be to union the three columns together and keep the distinct values. however i am having trouble getting the same result with a dax equation. 

I have been trying code similar to this:

count = 

UNION(VALUES(table[owner 1]),VALUES(table[owner 2]),VALUES(table[owner 3]))

however i always seem to get this error:

Calculation error in measure 'CRM_EA_Data'[Count]: A table of multiple values was supplied where a single value was expected.

[v-xicai]

Hi @Anonymous ,

You may unpivot the three owner columns into a separate column. Enter into Query Editor, click on columns owner 1, owner 2 and owner 3 at the same time, then choose "Unpivot Columns" option, you can rename this result column "Attribute" with "owner " . Don't forget to click the "Close & Apply" button. Then you can get count of owners using code DISTINCTCOUNT([owner]) .

Best Regards,

Amy

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

[Anonymous]

@v-xicai 

I'm DIrect Querying into a database and such transformations, unfortuantly are not allowed. This is why i needed a dax equation. We're working around however and bringing in a serperate reference table. 

-thank you.