Forum Discussion

avioti's avatar
avioti
Frequent Visitor
8 years ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Yes.  You could do it all within:

     

    Your Measure = DIVIDE(
    SUM('Sales'[Total_Sales]),
    DISTINCTCOUNT('Sales'[Code_Sales])
)
Anonymous's avatar
Anonymous
Not applicable
8 years ago

Yes.  You could do it all within:

 

Your Measure = DIVIDE(
    SUM('Sales'[Total_Sales]),
    DISTINCTCOUNT('Sales'[Code_Sales])
)
avioti's avatar
avioti
Frequent Visitor
8 years ago

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's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      avioti
      Frequent Visitor
      8 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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]