Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 =
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |