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

      • MFelix's avatar
        MFelix
        Super User
        7 years ago

        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