Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Count data based on criteria

Hello All,

 

I have two datasets.

 

The first one is a sheet with each possible article. Now I need to know the rows "Count" & "Distinctcount" based on criteria "ID". Count is counting each dataset based on the ID. Distinctcount is counting each distinct value based on ID. The row "Count" & "Distinctcount" is based on another data sheet (2. History). If there is no entry is should be displayed zero. Please see example in red below.

 

1. Overview of articles

IDCountDistinctcount

A-1

21
A-221
A-300
B-132
B-200

 

2. History

IDColour
A-1Red
A-1Red
A-1Yellow
A-2White
B-1White
B-1Red
B-1Red
A-2White

 

I really appreciate your help. Thank you very much in advance. SpartaBI  Do you know the answer?

 

BR

Need Help
  • AntonioM's avatar
    AntonioM
    4 years ago

    Hi Anonymous 

     

    Are you wanting to total up the distinct count of colour for each id? If so you can use 

    Distinct count = SUMX ( VALUES('Overview of articles'[ID]), CALCULATE(DISTINCTCOUNT(History[Colour])) + 0)

    which will add up the individual distinct counts.

     

     

     

     

    You can do this with a column. If you have the relationship ID - ID then

    COUNT = CALCULATE(COUNT(History[Colour])) + 0

     and

    Distinctcount = CALCULATE(DISTINCTCOUNT(History[Colour])) + 0

     should work again

     

     

3 Replies

  • AntonioM's avatar
    AntonioM
    Solution Sage
    4 years ago

    If you join the tables together on ID

     

    Then write two measures

     

    Count = COUNT(History[Colour]) + 0
    Distinctcount = DISTINCTCOUNT(History[Colour]) + 0

     

     

    Then you can create the table with Overview[Id], Count and Distinctcount. Is that what you're after?

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Antonio,

       

      thank you very much for the quick reponse.

      I need it as a extra coloum in the first table "Overview of articles". I tried the measure, but then there is another error. It wont summed up correct. I am not sure what the problem is, but building a sum from measure and coloums doesnt work correctly.

       

      Do you have a idea how to solve it as a extra coloumn?

      • AntonioM's avatar
        AntonioM
        Solution Sage
        4 years ago

        Hi Anonymous 

         

        Are you wanting to total up the distinct count of colour for each id? If so you can use 

        Distinct count = SUMX ( VALUES('Overview of articles'[ID]), CALCULATE(DISTINCTCOUNT(History[Colour])) + 0)

        which will add up the individual distinct counts.

         

         

         

         

        You can do this with a column. If you have the relationship ID - ID then

        COUNT = CALCULATE(COUNT(History[Colour])) + 0

         and

        Distinctcount = CALCULATE(DISTINCTCOUNT(History[Colour])) + 0

         should work again