Home › Forums › Power Pivot › Distinct Count

Tagged: ,

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

Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
    Posts
  • April 17, 2019 at 2:22 am #15506

    sjhc1177
    Participant
    • Started: 25
    • Replies: 28
    • Total: 53

    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]
    )

    April 17, 2019 at 2:51 am #15508

    sjhc1177
    Participant
    • Started: 25
    • Replies: 28
    • Total: 53

    I’ve attached a screenshot of the table.

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

The forum ‘Power Pivot’ is closed to new topics and replies.