Count repeated

[Anonymous]

Good afternoon,
I have a table with DATE, MONTH, NO CONTAINER (alphanumeric). I need to calculate the total containers per month. According to the text, January has 10 records and I need the answer to be 3 containers (1-18, 2-18, 3-18)

MONTH         NO CONTAINER

Enero             1-18

Enero             1-18

Enero             1-18

Enero             2-18

Enero             2-18

Enero             3-18

Enero             3-18

Enero             3-18

Enero             3-18

Enero             3-18

[MFelix]

Hi @Anonymous,

You can add the NO CONTAINER columb to your visuals and select the distinct count summarization or create the following measure

Total containers = DISTINCTCOUNT ( Table[NO CONTAINER] )

Regards
MFelix

[Anonymous]

Thanks MFelix, Excellent !!

I have a question: do you know why the totals are not adding up?

Thank you for your attention and collaboration

[MFelix · Accepted solution]

Hi @Anonymous,

When you make a measure the calculations for total are make based on context so when you are in the totals you are calculating the distinct count of the full data set for january so if NO CONTAINER has only 3 values the total will return 3 you need to make this measure:

Total Containers =
IF (
    HASONEFILTER ( Table[Cliente] );
    DISTINCTCOUNT ( Table[NO CONTAINER] );
    SUMX ( ALL ( Table[Cliente] ); DISTINCTCOUNT ( Table[NO CONTAINER] ) )
)

Regards,

MFelix

[Anonymous]

Excellent MFelix. You do not know how much you have helped me. Very grateful, very kind

[MFelix]

Hi @Anonymous,

One thing I forgot, if you want to have a slicer/filter over the Cliente column instead of ALL use ALLSELECTED the rest of the syntax is the same.

ALL is gathering all the dataset and going over the filter selections.

Regards

MFelix

[Anonymous]

Thanks MFelix