Forum Discussion

mmyers2's avatar
mmyers2
Advocate I
8 years ago

Count by Measure Group

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.

Need Help

8 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    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's avatar
      mmyers2
      Advocate I
      8 years ago

      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.