locked
Powerpivot Help - Distribution report RRS feed

  • Question

  • I have a pivot table that i built in powerpivot. I have sales people in my organization that have a sales territories. We have about 800 different products. I want to create a distribution report that shows where they have and where they havent sales for a particular item. If there has been a “sale” , i want that # to be represented by the #1 so that i can divide this number into the number 1 which would be the value for each individual store (see below). I have a sales table that contains the transactional sales data: ie date, Store, rep, units sold, etc and i have relationship with a table that has all of the stores we sell to along with the rep that sells to the store. Please Help…Thank you

    Rep: 1 Store               Sales                Distribution
               Store 1    1         1                     100%
               Store 2    1         1                     100%
               Store 3    1         0                       0%
                 Total     3          2                      66%

    Rep: 2 Store                Sales                Distribution
               Store 5    1          1                     100%
               Store 6    1          0                       0%
               Store 7    1          0                       0%
               Total       3           1                      33%

    Monday, January 7, 2013 5:20 AM

Answers

  • The logic in the calculated measures groups by Rep and Store and counts distinct products.  This was based on your original question about counting distribution by Rep and across Reps. 

    In the pivot you provided, you aren't grouping or filtering by Rep at all.  That explains why you aren't getting 1s and 0s. 

    Ultimately, the model you have will answer many questions.  If you no longer care about calculating at the Rep level and all you really want is to see if any products were sold in a store, then just using the distinct count of products should work for you (as long as you are slicing by product).

    Another approach is to simply do a countrows on your sales table to see if you had any sales at all for whatever you're slicing by.  Then use an IF statement to return 1 if the countrows > 0, else return 0.

    HTH


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com



    Monday, January 14, 2013 5:03 PM
    Answerer

All replies

  • Do you want to calculate a percentage of total sales?


    The Data Specialist (Blog)

    Monday, January 7, 2013 11:49 AM
  • Welll...Yes....I dont want the sales figures to show ie in dollars.  I just want the #1 to appear if there has been a sales, no matter how big or how small.  If I didnt have a transaction, then that data wont be in my sales data (which is a million rows of info) and some thats where i need to have all of my customers appear even if there is no sales
    Tuesday, January 8, 2013 12:48 AM
  • John -

    This can be easily solved with a dimensional model where Rep and Store are dimensions shared by the Sales facts and StoreCoverage facts like this:

    With that, you can create calculated measures for Distinct Count of Stores in Sales and in Sales Coverage.

    CountDistinctStore:=DISTINCTCOUNT(Sales[Store])
    
    CountDistinctCoverageStore:=DISTINCTCOUNT(StoreCoverage[Store])

    Then, your desired "Distribution" measure is simply DistinctCount of Sales Stores over DistinctCount of Coverage Stores.

    Distribution:=[CountDistinctStore] / [CountDistinctCoverageStore]

    With that, you can slice by Rep and Store (from the shared dimensions), and produce the following:

    I kept it simple with Rep and Store like you're example above, but if you needed to slice or filter by products or dates, you can include them as shared ("conformed") dimensions as well.

    Let me know if that helps.


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Tuesday, January 8, 2013 5:51 AM
    Answerer
  • John -

    I got an email about another reply on this thread from you that looks to be deleted now.  Regarding the need for product level stats.  Assuming that's still the requirement, here's what I'd suggest.

    Add a product table with all of your 800 products.  Include the product key in your Sales table.  And add Product to the StoreCoverage table.  Create a relationship from Sales to Product.  And from StoreCoverage to Product.  The model would look like this now.

     

    Now create the following measures:

    CntDistinctProduct:=DISTINCTCOUNT(Product[product]) CntDistinctSalesProduct:=
    SUMX(SUMMARIZE(Sales,Sales[Rep],Sales[Store]
    ,"DstProduct",[CntDistinctProduct]
    )
    ,[DstProduct]
    ) CntDistinctSalesCoverageProduct:=
    SUMX(SUMMARIZE(StoreCoverage,StoreCoverage[Rep],StoreCoverage[Store]
    ,"DstProduct",[CntDistinctProduct]
    )
    ,[DstProduct]
    ) Distribution:=
    IF([CntDistinctSalesProduct] = 0 && [CntDistinctSalesCoverageProduct] > 0 ,0 ,[CntDistinctSalesProduct] / [CntDistinctSalesCoverageProduct] )

    Note, for Distribution, using an IF to force the zeros you wanted instead of blanks distribution %.

    With that, you can produce a pivot like this:

     

    Let me know if that helps.


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com



    Tuesday, January 8, 2013 2:32 PM
    Answerer
  • Hi Brent,

    With the new measures provided, this isnt working for me now??  I no longer get only 1's or 0's for the CntDistrinctSalesCoverageProduct measure.   I am a little confused on how i should add the products into "StoreCoverage"..I want to have the Products in Columns and in Slicers..   End result to look something like this..... 


    Thursday, January 10, 2013 12:08 AM
  • The tables in the model are in the image above on the left side of the worksheet.  The coverage table is a factless fact that contains the intersection of all of the products that can be sold at a given store by a given rep. 

    And to get the pivot format you're asking for now, just drag product from the rows axis and drop it on columns.  You'll get exactly that.


    Brent Greenwood, MS, MCITP, CBIP // Please mark correct answers and helpful posts // http://brentgreenwood.blogspot.com

    Thursday, January 10, 2013 5:13 AM
    Answerer
  • Hi Brent,

    The distinct count isnt working for me (see below).  Not sure if i missed something with [DstProduct] (is this a measure that i should have created?).  I have provided the diagram view (do i need to delete all of columns is my Items, Stores, Territory fields??

     

    Thursday, January 10, 2013 6:18 PM
  • Model looks fine.  Although the additional snowflakes could complicate things. 

    Post the DAX for your measures and should be able to point you in the right direction. 

    And yes, I created a separate distinctcount measure on the product table that is called by the Sales and SalesCoverage distinctcount measures.  This isn't mandatory, but simplifies things.  Centralizes the calc logic in case you need to modify a core measure and have it affect multiple dependent measures.  And allows your SUMX measure to get an implicit CALCULATE by calling a separate measure, instead of having to explicitly call CALCULATE.


    Brent Greenwood, MS, MCITP, CBIP // Please mark correct answers and helpful posts // http://brentgreenwood.blogspot.com


    Thursday, January 10, 2013 6:49 PM
    Answerer
  • Here you go...

    FYI - Name/Store name are the same thing

    Thursday, January 10, 2013 8:14 PM
  • Ok.  Looking at the pivot above, looks like you've changed directions a bit.

    Going back to your original question.  If you create a simple pivot that has Rep and Store on Rows, do you get the expected result? 

    Once the simple pivot is working, you can introduce additional logic / changes to slice the data in different ways if needed.  But I'd suggest getting the basic model working first. 


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Friday, January 11, 2013 1:37 AM
    Answerer
  • Hi Brent,

    The logic seemed to work properly on the original model that was introduced.  The issues started to occur on the second method....

    Friday, January 11, 2013 6:13 AM
  • Your original question was to slice by Rep and Store, correct?  That's why the formulas above group by Rep and Store.

    And regarding the second version of the distinctCount calcs above, I added the SUMMARIZE to make sure that the GrandTotals still had the Rep included.  Otherwise your counts and % would be off.  Atleast from the perspective of individual sales rep distribution.

    The latest screen shot you provided of your pivot seems to have introduced several other attributes, but the naming convention does not make it clear what you are slicing by.   To get to a solution, please specify exactly what you have on rows, columns, filters & slicers.  Or simply share the workbook. 


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Friday, January 11, 2013 2:16 PM
    Answerer
  • The logic in the calculated measures groups by Rep and Store and counts distinct products.  This was based on your original question about counting distribution by Rep and across Reps. 

    In the pivot you provided, you aren't grouping or filtering by Rep at all.  That explains why you aren't getting 1s and 0s. 

    Ultimately, the model you have will answer many questions.  If you no longer care about calculating at the Rep level and all you really want is to see if any products were sold in a store, then just using the distinct count of products should work for you (as long as you are slicing by product).

    Another approach is to simply do a countrows on your sales table to see if you had any sales at all for whatever you're slicing by.  Then use an IF statement to return 1 if the countrows > 0, else return 0.

    HTH


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com



    Monday, January 14, 2013 5:03 PM
    Answerer
  • Excellent, thanks for the help.  Its working properly.  I've also decided to add a time dimension element now that this is working properly.  I want to be able to roll timeframes via a slicer, ie 3,4,6,12,18 months .  i assume i need to add new dates tables?
    Tuesday, January 15, 2013 7:51 AM