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 ...
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

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

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