Home Forums Power Pivot Distinct Count Issue

This topic contains 1 reply, has 2 voices, and was last updated by  tomallan 1 month, 1 week ago.

Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
    Posts
  • #18050

    IH8DATA
    Participant
    • Started: 4
    • Replies: 1
    • Total: 5

    Good morning,

    I am trying to figure out how many points of distribution (distinct count of customers) I have for a given product within the most recent 90 days.

    I have three lookup tables:

    • sku_data – unique values for all of my products (how I link my product ID)
    • customer_table – unique values for all of my customers (how I link my customer ID)
    • calendar_data – my date table

    My fact (data) table is ‘sales_data’ and it is at the invoice level.  That means that a customer can have a row for each product they bought within a day and a column for how many cases were deliverd and how much they were charged.

    Here is my formula.

    POD Delv Cases 2019:=CALCULATE(
    DISTINCTCOUNT( sales_data[SD_CUST_ID] )
    , FILTER( customer_table , [Delivered Cases] >= 1 )
    , FILTER( ALL( calendar_data[Date] ) , calendar_data[Date] > TODAY() – 90 )
    )

     

    Where – [Delivered Cases] = SUM( sales_data[delv_cases])

     

    Can anyone help me understand why a product that has no delivered cases in the last 90 days would show anything other than 0?

     

    Thank you

     

     

    #18051

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2566
    • Total: 2573

    Could you put up some sample (anonymous) data in a workbook or pbix file?

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.