Home Forums Power Pivot Distinct Count

This topic contains 1 reply, has 1 voice, and was last updated by  sjhc1177 3 months ago.

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

    sjhc1177
    Participant
    • Started: 17
    • Replies: 17
    • Total: 34

    Hi below is a DAX measure I use to count the number of unique sales each day.

    Unfortuantly I can’t seem to get it to work properly. Each day on it’s own is correct as to the number of unique transactions. But when it totals a week or more the amounts for each day don’t equal the calculated day.

    Thoughts?

     

    TRANS# DISTINCT COUNT:=SUMX (
    ADDCOLUMNS (
    SUMMARIZE (
    ‘SALES TABLE’,
    ‘SALES TABLE'[TRANS#],
    “NetUnitsSold”, SUM ( ‘SALES TABLE'[UNITS SOLD] )
    ),
    “SalesTranCount”, IF ( [NetUnitsSold] > 0, 1, IF ( [NetUnitsSold] < 0, -1 ) )
    ),
    [SalesTranCount]
    )

    #15508

    sjhc1177
    Participant
    • Started: 17
    • Replies: 17
    • Total: 34

    I’ve attached a screenshot of the table.

    Attachments:
    You must be logged in to view attached files.
Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.