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_sales, however, I would like to count the unique code sales. Would it be possible to use sum + count distinct?

 

 

 

Thanks

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

5 Replies

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