Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Count repeated

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

 

 

Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    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

6 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    7 years ago
    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        MFelix
        Icon for Super User rankSuper User
        7 years ago

        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