Forum Discussion

LS1700's avatar
LS1700
Icon for Resolver I rankResolver I
9 years ago
Solved

sum by group and count if

Hi, 

 

I would like to have a measure that can tell me how many cusotmer have not sold a single unit in total.

 

The measure ( I think) should do the sum of the sales by customer and then count those who have sales=0.

 

Any idea?

 

in the below case it should be =1.

 

 

CustomerModelSales
AX1
AY0
AZ1
BX1
BY1
BZ1
CY1
DX0

 

 

thanks

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    I didn't try this... but it sounds kinda nice...?  :)

     

    Total Sales = SUM(MyTable[Sales])

    Losers = CALCULATE(DISTINCTCOUNT(MyTable[Customer]), FILTER(VALUES(MyTable[Customer]), [Total Sales] = 0))

     

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    I didn't try this... but it sounds kinda nice...?  :)

     

    Total Sales = SUM(MyTable[Sales])

    Losers = CALCULATE(DISTINCTCOUNT(MyTable[Customer]), FILTER(VALUES(MyTable[Customer]), [Total Sales] = 0))

     

     

    • AlanBaker's avatar
      AlanBaker
      Frequent Visitor
      9 years ago

      Brilliant! Works like a charm and JUST what I was looking for....