Totals distinct count result instead of normal count

[Justas4478]

Morning I have this DAX measure:

SKU Count = CALCULATE(COUNTA('Product'[SKU Number]))
I am getting distinct count result in totals even tho I am trying to do normal count:

Expected total result should be around ~116
As you see in the table it count every sku number but only return distinct in total.
I tried to change DAX multiple times, but I am getting always same totals.
I am not sure how to change it to make it work.
To note: my 'SKU Number' column exist in 'Product' table, other data uses 'Outbound Delivery' table.

Thanks

[v-tangjie-msft]

Hi @Justas4478 ,

Thanks @Uzi2019  for the quick reply.

By your description, your measure [SKU Count] is displaying the correct count on each row, but the total count is incorrect, right?

You can then create a new measure and place the new measure on the visual object and observe if the totals are correct.

NewMeasure = SUMX(VALUES(Date Table[date]), [SKU Count measure])

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Justas4478]

@v-tangjie-msft Sorry for taking so long to reply, something came up.
I tried your solution and it looked like it is working ok for small sku count, but when I switched back to unfiltered amount of sku that we would be looking at.
It could not give correct results.
Expected row count would be 78704, and result I get is 108358.
I dont know maybe some outside filters are ignored or what other reason could it be.
I as well tried to use different sumx instead of date and results were either lower or higer than expected row count.
I attached sample file as well.
https://we.tl/t-MpfvujM5lj 

[v-tangjie-msft]

Hi @Justas4478 ,

Please try to create a new measure.

NewMeasure =
CALCULATE([count measure],ALL('Table'))

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Justas4478]

@v-tangjie-msft Morning, I tried your measure:

Total count = CALCULATE('Product'[SKU Count],ALL('Product'))
This is results that I got:

As you see it is not acting as hoped for.

[Uzi2019]

Hi @Justas4478 

can you share the pbix of sample data in excel??

[Justas4478]

@Uzi2019 Unfortunatelly I cant share pbx file since our data is using live connection model.
I can only share data in the table:
https://we.tl/t-zEZXAWFyBf 
It might be something with realationships between tables that could be causing totals to be distinct or just DAX that I am using.

[Uzi2019]

Hi @Justas4478 
Can try with different measure

Count_ = Calculate(Count(SKU Number), Allexcept(date))


Question: How do you know your answer is 116 is it number of dates coming from different table

[Uzi2019]

Hi @Justas4478 

I think you just have only 4 SKU in product table , correct??
but you have 116 row in Outbound Delivery table ??

 Correct me if i am wrong.

[Justas4478]

@Uzi2019 Yes that is correct it is 116 total amount of rows.

[Uzi2019]

Hi @Justas4478 

Instead of counting SKU Number try to count date from Outbound Delivery table.

Or countrow fucntion to count the number of rows in a table.

It might give you 116 as a result.

I hope I answered your question!

[Justas4478]

@Uzi2019 I tried counting 'Outbound Delivery' table and this is result I got:

I tried later as well adding FILTERS in to to calculation but that did not change the result.
The date is not from outbound delivery table it is from 'Date' table that outbound delivery table is connected by date key.

[Uzi2019]

Hi @Justas4478 
so what fileds you have in outbound delivery??? try to take that into count function .

I dont know about your modeling that's why asking for pbix. I undertand you have restirction you cant share it.

[Justas4478]

@Uzi2019 These are the fields available in 'Outbound Delivery' table.