How do I show the sum of a count measure?

[Noyer]

Hi All

I have a data table that shows stock volume by SKU by store by week and I want to show a count of the number of stores that have a given SKU in stock in a given week.

For a matrix table I have created a count measure:  if(sum('Combined Weekly Stock'[Physical Quantity])>0, 1, 0)

This returns 1 or 0 depending on whether the SKU was in stock in each store in each week. 

However, this same logic also applies to the total row in the matrix table:  Total physical quantity on the total row is above 1 unit so the total row shows 1.

How do I get the total row to sum all the individual counts?  I would like the measure to work as it does on all rows except totals where I need it to sum the counts of the sub-rows.

The stock table holds SKUs with no stock, so I can't count occurences or rows.

Thanks!

[amitchandak]

@Noyer , Try like

sumx(values(Table[Store]),if(sum('Combined Weekly Stock'[Physical Quantity])>0, 1, 0))

[Fowmy]

@Noyer 

This should work:

Modified Measure = 
 
 SUMX(
     'Combined Weekly Stock',

 if(
     sum('Combined Weekly Stock'[Physical Quantity])>0,
    1, 0)
 )

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[mahoneypat]

Assuming your Stores are on the rows, you can use an approach like this to get the "total" to show correctly.  It references your existing measure, and should return the same values on the rows but the sum of those in the total.  Replace with your actual table/column/measure name.

NewMeasure = SUMX(VALUES(Table[Store]), [Count Measure])

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Noyer]

@mahoneypat @amitchandak 

Thank you for the quick reply - your solution nearly gets me there but my count measure doesn't behave as expected.  I have used:

sumx(values('Combined Weekly Stock'[Store]), if(sum('Combined Weekly Stock'[Physical Quantity])>0, 1, 0))

However, this then counts all stores with that producut listed, even if the quantity is nil.  I've inlcuded a screen grab, below, plus notes on the four columns

There are 18 stores in this list but two of them don't have stock (one with nil, one with stock missing at -1).  I would like the total row to show 16, not 18.

Col 1:  This is the quantity for this product in each store

Col 2:  This is the basic count measure (1 if quantity is >0, otherwise 0) but you can see the total is 1, not 16

Col 3:  Just a count of the store (so per row is 1)

Col 4:  This is my application of your solution sumx(values('Combined Weekly Stock'[Store]), if(sum('Combined Weekly Stock'[Physical Quantity])>0, 1, 0))  but even though two of the rows show 0 (for not having more than 0 stock), the total for this column is 18, not 16

How do I get the total to ignore rows with 0?

Thanks again

Noyer

[mahoneypat · Accepted solution]

You need to either reference your existing measure, or wrap your expression in a CALCULATE as follows

sumx(values('Combined Weekly Stock'[Store]), if(CALCULATE(sum('Combined Weekly Stock'[Physical Quantity]))>0, 1, 0))

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Noyer]

@mahoneypat  Thank you for taking the time to come back to me - works perfectly!