DAX SUM/distinct(Count)

[avioti]

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

[Anonymous · Accepted solution]

Yes.  You could do it all within:

Your Measure = DIVIDE(
    SUM('Sales'[Total_Sales]),
    DISTINCTCOUNT('Sales'[Code_Sales])
)

[avioti]

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

[Anonymous]

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)"

[avioti]

I understand, but i would like to have this info in a table. Is it possible?

[Anonymous]

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]