Forum Discussion

akkitek's avatar
akkitek
Helper III
2 years ago
Solved

Adding a count column based on values from another column

Hi Everyone, I have a data model with few different tables directly or indirectly connected each other. I have built a table visual with three columns, each coming from a different dataset. Clien...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi akkitek 

     

    Thanks for the reply from lbendlin .

     

    If I understand correctly, you are trying to calculate how many Engagement Divisions there are per client.

     

    What are your calculation criteria? Do you not double count the same division? I'm a little confused about the expected result you are giving, if I understand you correctly, then the expected result for A-00103 should be 2, not 3? 

     

    Since I found that the FACT_Fin_Data_by_Mo table contains the three columns used in the visualization chart, why do you have to use three tables instead of the FACT_Fin_Data_by_Mo table? Here's what I've done so far, I used the three columns from the FACT_Fin_Data_by_Mo table and then I created the following measure:

     

    of divisions per client = CALCULATE(DISTINCTCOUNT(FACT_Fin_Data_by_Mo[Engagement Division]), ALLEXCEPT(FACT_Fin_Data_by_Mo, FACT_Fin_Data_by_Mo[Client Number]))

     

     

    Output:

     

    You can also use a calculated column if you want, as shown here:

     

    Please feel free to correct me if I have misunderstood you.

     

    Best Regards,
    Yulia Xu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi akkitek 

 

Thanks for the reply from lbendlin .

 

If I understand correctly, you are trying to calculate how many Engagement Divisions there are per client.

 

What are your calculation criteria? Do you not double count the same division? I'm a little confused about the expected result you are giving, if I understand you correctly, then the expected result for A-00103 should be 2, not 3? 

 

Since I found that the FACT_Fin_Data_by_Mo table contains the three columns used in the visualization chart, why do you have to use three tables instead of the FACT_Fin_Data_by_Mo table? Here's what I've done so far, I used the three columns from the FACT_Fin_Data_by_Mo table and then I created the following measure:

 

of divisions per client = CALCULATE(DISTINCTCOUNT(FACT_Fin_Data_by_Mo[Engagement Division]), ALLEXCEPT(FACT_Fin_Data_by_Mo, FACT_Fin_Data_by_Mo[Client Number]))

 

 

Output:

 

You can also use a calculated column if you want, as shown here:

 

Please feel free to correct me if I have misunderstood you.

 

Best Regards,
Yulia Xu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • akkitek's avatar
    akkitek
    Helper III
    2 years ago

    Anonymous Thank you so much for your response. 
    The calculation criteria is that every client can have multiple projects and each project can have different or same divisions. So, although there are 2 distinct divisions the expected result for A-00103 should be 3 since there are 3 different projects. 

    Based on your calculated column, I tried changing the "distinctcount" function to "count" but it doesn't work. Should I be adding another layer to this column calculation? 

    Yes, these columns come from one dataset so its doable. But would this also work if they were in different tables?

    Thanks! really appreciate the help:)

  • akkitek's avatar
    akkitek
    Helper III
    2 years ago

    Thanks for the help Anonymous !