Forum Discussion

BI-Bola's avatar
BI-Bola
Frequent Visitor
6 years ago
Solved

Count Distinct where sum is

I am trying to get a distinct count of values where the sum of values is not zero

 

Below I have a basic distinct count: 

 

Distinct Count = CALCULATE(DISTINCTCOUNT(AccountTransactions[Segment]), AccountTransactions[Amount] <> 0)
 
How would I create a similar function where the I return a distinct count for "Segment" where the SUM of "Amount" is not 0?
 
For the sake of clarity a sample table is below.
 
SegmentAmountvar1var2
A10examplerandom1

A

1example 2random 2
B0example 3random 3
C1example 4random 4
D2example 5random 5
D2example 6random 6
 
For the above table a distinct count of segment values where the SUM of the amount column is not zero would be 3.
 
I want to display this information in a card. I have tried the below formula but it will not work.
 
CALCULATE(COUNTROWS(distinct(AccountTransactions[Segment4])), FILTER(AccountTransactions, SUM(AccountTransactions[Amount])<>0))
 
After some thought I realised that this is because while the rows have been narrowed with "distinct" it isn't eliminating duplicate "Segment" lines as the additional columns contain unique information foreach row. So my result is a much larger value. 
 
I could create a mirrored table that only pulls through the "Segment" and run the formula based on the relationship between that and the main table but I want to know if there's another way. 
 
Thanks
Need Help
  • BI-Bola's avatar
    BI-Bola
    6 years ago

    Thanks for your suggestions all but strangely none worked with my dataset. I'm not sure if it's the size of the dataset or the fact that it is running through a direct query causing the issue.

     

    To get around it I created an additional table from the main query using a combination of the CALCULATETABLE, SUMMARIZE and FILTER functions and put a count card visual pulling data from new table.

13 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    share some sample data and expected output.

     

     

  • vanessafvg's avatar
    vanessafvg
    Community Champion
    6 years ago

    do you want a distinct count or row count?  distinct count on segment will just count the segment?

  • BI-Bola's avatar
    BI-Bola
    Frequent Visitor
    6 years ago

    Anonymous 

    Original post updated

     

    vanessafvg 

    I am after a distinct count of the segment column values where the sum of "Amount" is not equal to zero

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Create measure

     

    measure= Sumx(summerize(table,table[sagement],"Total",sum(table[Amount]),If(Total>0,1,0))

     

    Thanks & regards,
    Pravin Wattamwar
    www.linkedin.com/in/pravin-p-wattamwar

    If I resolve your problem Mark it as a solution and give kudos.

    • BI-Bola's avatar
      BI-Bola
      Frequent Visitor
      6 years ago

      Doesn't seem to work, spelling of functions were corrected

       
      MEASURE = SUMX(SUMMARIZE(AccountTransactions,AccountTransactions[Segment],"Total", SUM(AccountTransactions[Amount]),If(Total>0),1,0))
       
      error "The syntax for ')' is incorrect."
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        it's my bad.

         

        I forgot to close bracket.

         

        measure= Sumx(summerize(table,table[sagement],"Total",sum(table[Amount])),If(Total>0,1,0))

         

        Thanks & regards,
        Pravin Wattamwar
        www.linkedin.com/in/pravin-p-wattamwar

        If I resolve your problem Mark it as a solution and give kudos.

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    6 years ago

    Hi BI-Bola ,

     

    You can try the DAX below:

    Measure = CALCULATE(COUNTROWS(DISTINCT('sample'[Segment])),FILTER('sample','sample'[Amount]<>0))
     

     

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

  • BI-Bola's avatar
    BI-Bola
    Frequent Visitor
    6 years ago

    I have expanded the example table so that it is more in line with my dataset, apologies if it was unclear.

     

    V-lianl-msft 

     

    Thanks for the response but your formula does't seem to work for the updated table

     

    • V-lianl-msft's avatar
      V-lianl-msft
      Community Support
      6 years ago

      Hi BI-Bola ,

       

      After my test, I can get the expected results.

      If my understanding is wrong, please share the result you expect.

       

      Best Regards,
      Liang
      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
        6 years ago

        Hi BI-Bola 

         

        Heve you tried the solution which i suggested.

         

        measure= Sumx(summerize(table,table[sagement],"Total",sum(table[Amount])),If(Total>0,1,0))

         

        Thanks & regards,
        Pravin Wattamwar
        www.linkedin.com/in/pravin-p-wattamwar

        If I resolve your problem Mark it as a solution and give kudos.

  • BI-Bola's avatar
    BI-Bola
    Frequent Visitor
    6 years ago

    Thanks for your suggestions all but strangely none worked with my dataset. I'm not sure if it's the size of the dataset or the fact that it is running through a direct query causing the issue.

     

    To get around it I created an additional table from the main query using a combination of the CALCULATETABLE, SUMMARIZE and FILTER functions and put a count card visual pulling data from new table.