Forum Discussion

loafers's avatar
loafers
Frequent Visitor
4 years ago
Solved

Distinct Count but exclude if total is 0?

Hi,

 

I'm looking to get the distinct count of customers by month from my dataset but want to not count it if the sum of the totals for a customer is zero. Sample data below. Customer A has 50 and -50 both in Month 1 for a net total of 0. If I drop the data into a matrix table, the total distinct count will be 4 since customer A shows up. Is there a way to get around it so that distinct count will just be 3? 

 

CustomerMonthAmount
A150
A1-50
A3100
B168
C161
D112
A413
B516
C373
D422
D462
C521
B230

 

Thanks

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    Hi,

    These measures work

    Total = SUM(Data[Amount])
    Measure = countrows(FILTER(VALUES(Data[Customer]),[Total]>0))

    Hope this helps.

3 Replies

  • VahidDM's avatar
    VahidDM
    Icon for Super User rankSuper User
    4 years ago

    Hi loafers 

     

    Try this measure:

    Measure = 
VAR _Count =
    COUNTA ( 'Table'[Customer] )
VAR _Zero_Month_Count =
    COUNTROWS (
        FILTER (
            SUMMARIZE (
                'Table',
                'Table'[Customer],
                'Table'[Month],
                "Sum Month", SUM ( 'Table'[Amount] )
            ),
            [Sum Month] = 0
        )
    )
RETURN
    _Count - _Zero_Month_Count

     

    Output:

     



     

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

    Appreciate your Kudos!! 

    All logo

    LinkedIn | Twitter | Blog | YouTube 

    FIFA World Cup - Medal Records 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    These measures work

    Total = SUM(Data[Amount])
    Measure = countrows(FILTER(VALUES(Data[Customer]),[Total]>0))

    Hope this helps.

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, loafers ;

    You could create measures as follow:

    sum = CALCULATE( SUM('Table'[Amount]),FILTER(ALLSELECTED('Table'),[Customer]=MAX('Table'[Customer])))
    count = CALCULATE(DISTINCTCOUNT('Table'[Customer]),FILTER('Table',[sum]<>0))

    The final output is shown below:

     


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.