locked
Split distinct count into fractions of a count RRS feed

  • Question

  • Hello,

    Our company looks a lot at our sales deal count number. In all of the reporting we do, the count is always present. However, some of the metrics we look at will split up a deal. Say a deal spans across two sites and we want to see the metrics for the individual sites but still have the count of deals only show that as one.

    That's where I've become an avid user of the distinct count function within power pivot. However, I'm constantly being asked why if you add up all the count numbers in the pivot table it doesn't equal the total. Then I go into explaining all the reasons why a single deal might get split, so the total shown is only a unique count. What I'd like now is for the count in the pivot to split itself every time the deal id is split. So in the case above with 2 sites, the count next to those would each show 0.5. Or if the data was pivoted in another way that split a single lease into 5 rows, the count would be 0.2, etc.

    Thank you,


    - Addison

    Tuesday, July 26, 2016 3:35 PM

Answers

  • So you could create a measure like the following:

    =SUMX(Orders, 1/CALCULATE(COUNTROWS(Orders), ALLEXCEPT(Orders,Orders[OrderNumber]))


    However, I'm constantly being asked why if you add up all the count numbers in the pivot table it doesn't equal the total. Then I go into explaining all the reasons why a single deal might get split, so the total shown is only a unique count. 

    But even though it is possible to do a fractional calculation, my suggestion would to create a document with the explanation about how distinct counts work and post it on your intranet or send it to people that question the figures.

    Because you will most likely still get complaints from people that the figures are incorrect, Because an individual will probably know how many deals they worked on for a given period and your reports will now under report that. Say someone worked on 52 deals, 50 on their own and 2 with another person. Your report will now show 51 and you will get complaints that your reports are missing data.

    I suppose you can show both measures, but you will still get questions about why the two are different.... At the end of the day I think you will probably be better off with a distinct count and educating your users, but that's just my 2 cents :)


    http://darren.gosbell.com - please mark correct answers

    Tuesday, July 26, 2016 11:12 PM

All replies

  • So you could create a measure like the following:

    =SUMX(Orders, 1/CALCULATE(COUNTROWS(Orders), ALLEXCEPT(Orders,Orders[OrderNumber]))


    However, I'm constantly being asked why if you add up all the count numbers in the pivot table it doesn't equal the total. Then I go into explaining all the reasons why a single deal might get split, so the total shown is only a unique count. 

    But even though it is possible to do a fractional calculation, my suggestion would to create a document with the explanation about how distinct counts work and post it on your intranet or send it to people that question the figures.

    Because you will most likely still get complaints from people that the figures are incorrect, Because an individual will probably know how many deals they worked on for a given period and your reports will now under report that. Say someone worked on 52 deals, 50 on their own and 2 with another person. Your report will now show 51 and you will get complaints that your reports are missing data.

    I suppose you can show both measures, but you will still get questions about why the two are different.... At the end of the day I think you will probably be better off with a distinct count and educating your users, but that's just my 2 cents :)


    http://darren.gosbell.com - please mark correct answers

    Tuesday, July 26, 2016 11:12 PM
  • Finally getting around to looking at this again and using Darren's solution. The level at which I wanted the ID split was a higher level than how it came through in my data set. As an FYI here's the final solution I ended up modifying from Darren:

    =SUMX(SUMMARIZE(Orders,Orders[Site],Orders[OrderNumber]), 1/CALCULATE(COUNTROWS(SUMMARIZE(Orders,Orders[Site],Orders[OrderNumber])), ALLEXCEPT(Orders,Orders[OrderNumber],Orders[Type])))

    The ALLEXCEPT I needed to enter in any filters I was using that still applied to the subset of data.

    Thanks again Darren!


    - Addison

    Friday, August 18, 2017 4:31 PM