Measure for Distinct Count of number sold per ID

[lennox25]

Here is my sample table. There are further columns which I have omitted but this is the reason there are duplicate rows per ID

What I need is a measure that tells me the (distinct) Total No Sold per ID.  The result for -748902 Store No 521 Date 02/12/2022 should be 200. At present when I use my measure to calculate its telling me 600 sold which is incorrect. I cant remove what here looks like duplicate rows as there are other columns that need to remain in this table.

[v-yiruan-msft · Accepted solution]

Hi @lennox25 ,

You can create a measure as below to get it, please find the details in the attachment.

Count of number sold = 
VAR _tab =
    SUMMARIZE ( 'Table', 'Table'[ID], 'Table'[Sold] )
RETURN
    SUMX ( _tab, [Sold] )

Best Regards

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.  Show the expected result very clearly.

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.  Show the expected result very clearly.

[PhilipTreacy]

@lennox25 

It's always helpful to provide a sample of all the data we're working with.

So you have something like this, I've created a dummy column to represent the columns we can't see in your data

You said you are using a measure called Sold Total and the image you supplied shows a column called Sold, not the measure.

If I add the Sold column into the table and change the aggregation to MIN

Removing the dummy Value column gives this which I think is where you are having the issue with the measure

But as you can see the MIN of Sold column gives the answer you want doesn't it?  So you can remove the measure from the table and use the MIN of Sold column.  Or am I missing something?  

Regards

Phil

[FreemanZ]

hi @lennox25 ,

besides the four fields, do you have other fields for the table visual?

[PhilipTreacy]

Hi @lennox25 

Change the aggregation for the Sold column from SUM to MIN or MAX.

Also, would help if you provided the DAX you're using for the measure.

Regards

Phil

[lennox25]

@PhilipTreacy That doesnt work for me.

The measure I am using is 

Sold total  = SUM('table'[sold])