locked
EXCEL 2010 : Power Pivot : Count distinct RRS feed

  • Question

  • Hello,

    I am using Power Pivot to link sql server database and I would like know how to count distinct values with excel 2010.

    Also all decimals numbers returns 0 (is there any solutions to param the local settings regarding the decimal separator)

    Have a good day

    Wednesday, September 28, 2016 9:27 AM

Answers

  • To count distinct values, use the DISTINCTCOUNT function, e.g.

    NumberOfCustomers:= DISTINCTCOUNT(Sales[CustomerID])

    As you ask for Excel 2010 specifically (don't remember if DISTINCTCOUNT is in that version), you can achieve the same result with

    NumberOfCustomers:= COUNTROWS(VALUES(Sales[CustomerID]))

    As for local settings, note that there are three options to change regional settings: in Windows, in Excel, and in Power Pivot (for the latter, there's a settings button on the ribbon in Excel). Play around with these to set the right settings.

    Wednesday, September 28, 2016 1:25 PM
    Answerer
  • Hi Gregory,

    In your scenario, which SQL Server PowerPivot for Excel 2010 in SQL Server 2012 and Excel 2013 support the DISTINCTCOUNT aggregation in DAX.

    In you are using SQL Server 2008 R2, a PivotTable based on PowerPivot data doesn’t offer a native Distinct Count aggregation. To calculate it, you need to create a calculated measure in the PivotTable. Here is a blog that is relevant only if you use PowerPivot for Excel 2010 in SQL Server 2008 R2. Please refer to the link below.
    http://sqlblog.com/blogs/marco_russo/archive/2009/12/26/distinct-count-measure-in-powerpivot-using-dax.aspx

    Regards,


    Charlie Liao
    TechNet Community Support

    Thursday, September 29, 2016 2:44 AM

All replies

  • To count distinct values, use the DISTINCTCOUNT function, e.g.

    NumberOfCustomers:= DISTINCTCOUNT(Sales[CustomerID])

    As you ask for Excel 2010 specifically (don't remember if DISTINCTCOUNT is in that version), you can achieve the same result with

    NumberOfCustomers:= COUNTROWS(VALUES(Sales[CustomerID]))

    As for local settings, note that there are three options to change regional settings: in Windows, in Excel, and in Power Pivot (for the latter, there's a settings button on the ribbon in Excel). Play around with these to set the right settings.

    Wednesday, September 28, 2016 1:25 PM
    Answerer
  • Hi Gregory,

    In your scenario, which SQL Server PowerPivot for Excel 2010 in SQL Server 2012 and Excel 2013 support the DISTINCTCOUNT aggregation in DAX.

    In you are using SQL Server 2008 R2, a PivotTable based on PowerPivot data doesn’t offer a native Distinct Count aggregation. To calculate it, you need to create a calculated measure in the PivotTable. Here is a blog that is relevant only if you use PowerPivot for Excel 2010 in SQL Server 2008 R2. Please refer to the link below.
    http://sqlblog.com/blogs/marco_russo/archive/2009/12/26/distinct-count-measure-in-powerpivot-using-dax.aspx

    Regards,


    Charlie Liao
    TechNet Community Support

    Thursday, September 29, 2016 2:44 AM