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

DAX SUM/distinct(Count)

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Yes.  You could do it all within:

 

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

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

Yes.  You could do it all within:

 

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

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
Not applicable

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
Frequent Visitor

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

Anonymous
Not applicable

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]

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors