Forum Discussion

raksharh's avatar
raksharh
Microsoft Employee
6 years ago
Solved

Choose Aggregate function conditionally

Hi Everyone,   I need to calculate count/DistinctCount based on a condition.  For example in below table, when Type is "Agreement1", I should determine total count of "Customer" for "Agreement1". ...
  • Anonymous's avatar
    Anonymous
    6 years ago

     

    Hi raksharh ,

     

    You can use the below measure:

     

    Count of Customer =
    var __disctinct = CALCULATE(DISTINCTCOUNT(Table1[Customer]),ALLEXCEPT(Table1,Table1[Type]))
    var __common = CALCULATE(COUNT(Table1[Customer]),ALLEXCEPT(Table1,Table1[Type]))
    return
    IF(TOPN(1,VALUES(Table1[Type]),VALUE(RIGHT(Table1[Type],1)),ASC) = "Agreement1", __common,__disctinct)
     
    This will only work for your above scenario. Incase you have more agreement types, the measure will not work.
    Logic is to find last digit of Type and convert in into number and then do a TOPN.
    So, Agreement 1 will be TOP1 when you do ASC and vice-versa.
     
    I think incase you have multiple agreement types go for multiple measures.
     
     
    Thanks and Regards,
    Harsh Nathani
     
     
     
Anonymous's avatar
Anonymous
Not applicable
6 years ago

 

Hi raksharh ,

 

You can use the below measure:

 

Count of Customer =
var __disctinct = CALCULATE(DISTINCTCOUNT(Table1[Customer]),ALLEXCEPT(Table1,Table1[Type]))
var __common = CALCULATE(COUNT(Table1[Customer]),ALLEXCEPT(Table1,Table1[Type]))
return
IF(TOPN(1,VALUES(Table1[Type]),VALUE(RIGHT(Table1[Type],1)),ASC) = "Agreement1", __common,__disctinct)
 
This will only work for your above scenario. Incase you have more agreement types, the measure will not work.
Logic is to find last digit of Type and convert in into number and then do a TOPN.
So, Agreement 1 will be TOP1 when you do ASC and vice-versa.
 
I think incase you have multiple agreement types go for multiple measures.
 
 
Thanks and Regards,
Harsh Nathani