DISTINCTCOUNT

[Anonymous]

This Data is about the Monthly Serviced units. One unit can get serviced multiple times in a month or year. However, when I count the total unit serviced I do not want the same unit being accounted twice. The DISTICNT COUNT works great for the Grand Column Total, however the DISCTINCT Function do not work on the monthly count ?. So 7 and 5 is the correct Disctinct count for the respective model for the Year, but the monthly listed unit come up as the totalcount. Any help is appreciated.

Measure = CALCULATE(DISTINCTCOUNT(DATA[SerialNumber]))

[Anonymous]

Worked like a Charm. Many Thanks Mate. Cheers

[Ashish_Mathur]

You are welcome.

[amitchandak]

@Anonymous , Not very clear, if you are asking why total do not match, so distinct count is calculated again for total to that might not sum up.

to sum up you need

 Measure = sumx(Values(DATA[Month]), CALCULATE(DISTINCTCOUNT(DATA[SerialNumber])))

or

Measure = sumx(summarize(DATA, DATA[Month], DATA[Model],"_1", CALCULATE(DISTINCTCOUNT(DATA[SerialNumber]))),[_1])

[Anonymous]

Here is the table and Desired Result for DISTINCT Count. 

[Ashish_Mathur]

Hi,

Based on that data that you have shared, =distinctcount(data[serial number]) should return the correct result for every month.  If not, then share the link from where i can download your PBI file.

[Anonymous]

Thank you Ashish for taking a look. Here is the File Link. 

https://1drv.ms/u/s!AqwXVHoKFnsWiHtR4-_cobMSr-a9?e=290jAe

[Ashish_Mathur]

Hi,

Try this measure

=

Count = if(HASONEVALUE(Data[Month]),DISTINCTCOUNT(Data[Model]),DISTINCTCOUNT(Data[SerialNumber]))

Hope this helps.

[Anonymous]

If you look at the monthly breakdown for 'Product A' it doesnt add up, even though the Total '7' is correct.

[Ashish_Mathur]

My result is correct.  33729 appears 3 times but in different months.  Check your data carefully.  If you still think i am incorrect, please share the exact expected result with an explanation.

[Anonymous]

'Product A' appears 9 times in the Dataset and it has 7 different SerialNumbers. DistinctCount Total of 7 is the correct number for Product A, but the monthly breakdown only shows 5.

Below is how the result needs to Display.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[ryan_mayu]

@Anonymous 

maybe you can create a column

count = if(CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Month]<=EARLIER('Table'[Month])&&'Table'[Model]=EARLIER('Table'[Model])&&'Table'[serialnumber]=EARLIER('Table'[serialnumber])))>1,0,1)

and put the new column in the value field

[Anonymous]

Thank you Ryan! Your recommended measure works great. However, if a unit(ServiceNumber) has been serviced twice in the same month, then the count shows ‘0’ instead of 1. Can it be fixed for it? Thanks again.