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])...
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.

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.