Count by Measure Group

[mmyers2]

I have a simple table with customer/SKU level sales data by week.

I have two measures:

Cust Avg Qty/Wkly = AVERAGEX(SUMMARIZE('Cust Usage','CUST Usage'[CUST_ID],
'CUST Usage'[PRODUCT_NUMBER], 'CUST Usage'[WEEK_OF_YEAR]),
CALCULATE(SUM('Cust Usage'[CUST_QTY_SHIPPED]))) 

CV Cust 2 = 
SWITCH(TRUE(),STDEVX.S(SUMMARIZE('Cust Usage','CUST Usage'[CUST_ID],'CUST Usage'[PRODUCT_NUMBER], 
'CUST Usage' [WEEK_OF_YEAR]),CALCULATE(SUM('Cust Usage'[CUST_QTY_SHIPPED])))/
'Cust Usage'[Cust Avg Qty/Wkly]<0.25,"1. Runner",
STDEVX.S(SUMMARIZE('Cust Usage','CUST Usage'[CUST_ID],'CUST Usage'[PRODUCT_NUMBER]
, 'CUST Usage'[WEEK_OF_YEAR]),CALCULATE(SUM('Cust Usage'[CUST_QTY_SHIPPED])))/
'Cust Usage'[Cust Avg Qty/Wkly]<0.5,"2. Repeater",
"3. Stranger") 
 

What I am having an issue with is counting the number of distinct products based on CV Cust 2. It works fine as long as I don't count products, but as soon as I try to count product, it shows all products as "3. Stranger", and does not show anything for the other 2 groups.

CV Cust 2 needs to stay a measure, as it needs to recalculate as more/less customers are selected in a slicer.

I have tried many different things, and have not gotten the results I am looking for.

I would also like to be able to put the data in a chart that shows the number of SKUs by CV Cust 2, but cannot use a measure as an axis. 

Any ideas/help would be greatly appreciated.

[v-jiascu-msft]

Hi @mmyers2,

How did you count a measure? Maybe you need a Summarize to count a measure. 

How did you create a visual? Maybe the formula can be simplified like this.

Cust Avg Qty/Wkly =
AVERAGEX ( 'Cust Usage', SUM ( 'Cust Usage'[CUST_QTY_SHIPPED] ) )

And the report visual coule be:

'CUST Usage'[CUST_ID],   'CUST Usage'[PRODUCT_NUMBER],  'CUST Usage'[WEEK_OF_YEAR]),  [Cust Avg Qty/Wkly]

About the visual you wanted, maybe you can create a table "MeasureValues" with the possible values of the measure. Then create a new measure like this:

New Measure =
VAR mvalue =
    MAX ( MeasureValues[column] )
RETURN
    SUMX ( 'table', IF ( 'table'[Old Measure] = mvalue, 1, 0 ) )

Please give it a try.

Best Regards!

Dale

[mmyers2]

I tried the simplified weekly average, and it did not summerize correctly. I need the measure to summarize based on customer, product, and week, regardless of what I use it.

When I use the formula for a visual, here is what I get, which is not correct. This is what I get no matter what formula I use. 

Surely there has to be a way to count records based on dynamic categories. 

Here is an example of how it should look. I used static data in a table to create this, but I need it to be dynamic.

 

[mmyers2]

Here is a link to a sample of the model.

I really need to get this working.

Dropbox Link

[mmyers2]

Anyone?

[v-jiascu-msft]

Hi @mmyers2,

You need add [CV Cust 2] as a calculated column. Then your measure [# of SKUs2] will work. You check it out in this file: https://1drv.ms/u/s!ArTqPk2pu-BkgUKnj3Uf866VcyMS.

Best Regards!

Dale

[mmyers2]

@v-jiascu-msft

Thank you for the reply, but a calculated column does not work as it is not dynamic.

CV Cust 2 needs to stay a measure, as it needs to recalculate as more/less customers are selected in a slicer.

[v-jiascu-msft]

Hi @mmyers2,

Measures need context while your visual doesn't have one. That's why the result is always "3. Stranger". You still can select the values of many columns in a slicer when you use a calculated column. What kind of dynamic do you expect? 

Best Regards!

Dale

[mmyers2]

@v-jiascu-msft

The only way I can think of to explain it, is with an example.

Let's say item A is a "1.Runner" when looking at all customers.

When I select customers 1, 2, and 3, item A is a "2.Repeater" because the calculation is only looking at the numbers for those customers.

Then, if I select only customer 1, item A is a "3.Stranger" because the calculation is only looking at the numbers for customer 1.

We have also been trying to do this with an ABC calculation based on number of sales, but get the same issue.