Forum Discussion

newgirl's avatar
newgirl
Icon for Post Patron rankPost Patron
4 years ago
Solved

Distinct Count per Group

Hi! I have a raw data of transactions of our clients, in which some clients belong to the same group which is why there's a column representing their grouping. Ex. Group 1 is an individual client while Group 5 has 4 clients. For my sample raw data, this means that there's a total of 10 groups (I forgot to include number 4).

 

The main table I need is to count how many groups are there with failed conditions. In this raw data, there are 3 conditions: FB (if client has Facebook), Past Due, and Minimum Points. Below is how a "failed condition" is defined:

  • FB - if a group has at least a "No", count as failed
  • Past Due - if a group has at least a "Yes", count as failed
  • Minimum Points - only if the WHOLE group has "No", then count as failed.

Below is a picture of the raw data, how it's counted, and the table I expect. 

 

For example with Group 11, we count with 1 failed for FB because the group has at least one "No" and we count with 1 failed for  Minimum Points because the whole group has "No". We count 0 for Past Due because there should be at least one "Yes" to be counted.

 

Another example with Group 9, we count with 1 failed for Past Due because the group has at least one "Yes". We count 0 for FB because there should be at least one "No" to be counted. We count 0 also for Mininum Points because only 1 of the groupmates had a "No". The WHOLE group should be a "No" to be counted.

 

 

Below is my raw data:

CategoryGroupClientSalespersonFBPast DueMinimum Points
Wholesalers111HomerYesNoYes
Wholesalers221HomerNoYesNo
Wholesalers331HomerYesNoYes
Wholesalers551HomerYesYesYes
Wholesalers552HomerYesYesYes
Wholesalers553HomerYesYesYes
Wholesalers554HomerNoYesYes
Wholesalers661HomerYesYesYes
Wholesalers662HomerYesNoNo
Wholesalers771HomerNoYesNo
Wholesalers772HomerNoYesNo
Wholesalers773HomerNoYesNo
Wholesalers881BartNoYesYes
Wholesalers882BartNoYesYes
Wholesalers883BartNoYesYes
Wholesalers991BartYesNoNo
Wholesalers992BartYesNoYes
Wholesalers993BartYesYesYes
Wholesalers994BartYesNoYes
Wholesalers10101BartYesNoNo
Wholesalers10102BartNoNoNo
Wholesalers11111BartNoNoNo
Wholesalers11112BartYesNoNo
Wholesalers11113BartYesNoNo

 

 

Hope somebody can help me out with the measure!

Need Help
  • HotChilli's avatar
    HotChilli
    4 years ago

    OK, I've created a column:

     

    AllNoinGroupMinPoints = var _Group = Sales[Group]
    var _rowsInGroup = COUNTROWS(FILTER(Sales, Sales[Group] = _Group))
    var _rowsInGroupWithNo = COUNTROWS(FILTER(Sales, Sales[Group] = _Group && Sales[Minimum Points] = "No"))
    RETURN
    IF(_rowsInGroup = _rowsInGroupWithNo, 1, 0)
     
    and then a measure to use the column:
    F_MinPTest = CALCULATE( DISTINCTCOUNT(Sales[Group]), Sales[AllNoinGroupMinPoints] = 1)
     

    Please test at your side.

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    4 years ago

    newgirl Seems like you should be able to use a measure along the lines of:

    COUNTROWS(DISTINCT(FILTER(ALL('Table'),<filter conditions>)))

or potentially:

COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER(ALL('Table'),<filter conditions>),"column",[Column])))

    I wasn't quite following the conditions.

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    4 years ago

    I think this will give you a pattern for each measure (sub in table, column names at your side):

    MeasureFTest = CALCULATE( DISTINCTCOUNT(TableQ[Group]), TableQ[FB] = "No")

    One of the other measures will be similar and one will have comparison to "Yes"

    Let me know how you get on

    • newgirl's avatar
      newgirl
      Icon for Post Patron rankPost Patron
      4 years ago

      Hi HotChilli !

       

      Thanks for your reply! These are the measures I made:

      F_FB = CALCULATE( DISTINCTCOUNT(Sales[Group]), Sales[FB] = "No")

F_PastDue = CALCULATE( DISTINCTCOUNT(Sales[Group]), Sales[Past Due] = "Yes")

F_MinP = CALCULATE( DISTINCTCOUNT(Sales[Group]), Sales[Minimum Points] = "No")

       

      Comparing this table with the expected output, only the 3rd measure is showing wrong results. Looks like it counts the group as long as it has a "No". However, I need it to count the group only if the whole group has a "No" .  Any suggestion on how to modify the 3rd measure?

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    4 years ago

    I've been trying to post a reply but it's not saving.  This is a test

     

    - edit : it was the code insert that was causing a problem.  I've posted it as text now 

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    4 years ago

    OK, I've created a column:

     

    AllNoinGroupMinPoints = var _Group = Sales[Group]
    var _rowsInGroup = COUNTROWS(FILTER(Sales, Sales[Group] = _Group))
    var _rowsInGroupWithNo = COUNTROWS(FILTER(Sales, Sales[Group] = _Group && Sales[Minimum Points] = "No"))
    RETURN
    IF(_rowsInGroup = _rowsInGroupWithNo, 1, 0)
     
    and then a measure to use the column:
    F_MinPTest = CALCULATE( DISTINCTCOUNT(Sales[Group]), Sales[AllNoinGroupMinPoints] = 1)
     

    Please test at your side.