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.
Hello Guys
Could you help me pls?
I have a table called sales and has column with total sales and another collumn code_sales. I would like to sum (total sales) and divide by the quantity of code_sales, however, I would like to count the unique code sales. Would it be possible to use sum + count distinct?
Thanks
Solved! Go to Solution.
Yes. You could do it all within:
Your Measure = DIVIDE( SUM('Sales'[Total_Sales]), DISTINCTCOUNT('Sales'[Code_Sales]) )
Yes. You could do it all within:
Your Measure = DIVIDE( SUM('Sales'[Total_Sales]), DISTINCTCOUNT('Sales'[Code_Sales]) )
Thanks for the response. It works fine.
Now i have another situation. How to show distinct values in a new column into my table?
Like below
For example:
Code Sales Distinct
1000 1
1000 0
2000 1
3000 1
4000 1
4000 0
This can be done with a simple Matrix visual. Put 'Code Sales' as your rows and also again into the values. Select the dropdown for the values version and choose "Count (Distinct)"
I understand, but i would like to have this info in a table. Is it possible?
Easy (but not the most optimal):
Start in Power Query (Edit query button). Make a new query and have the Source line be:
Source = #"Sales"
Now delete all other columns except for [Code_Sales]
Now remove duplicates
Now sort.
Close & Apply.
Link this new table with the Sales table
Create a new column and have it be:
My Distinct = [Your Measure]
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.