Forum Discussion

TD21's avatar
TD21
Helper II
6 years ago

Cumulative Count based on multiple criteria

Need assitance calculating cumulative count with multiple filtering criteria to be used in a chart.

 

I'm working with 2 tables:

Table #1: DATE_MASTER (it's a date table)

Table #2: SALES_EXPORT (contains the following fields)

  • ORDER_DATE
  • CUSTOMER_NAME
  • GENDER
  • CHANNEL (i.e. Web, Mobile App, Store)
  • SHIPPING_METHOD (i.e. Store pick-up, UPS, Fedex)

Below is my measure to calculate cumulative count:

 

SALES_CUMULATIVE_COUNT:=
IF (
MIN ( DATE_MASTER[Date] ) <= CALCULATE ( MAX ( [ORDER_DATE] ), ALL ( SALES_EXPORT ) ),
CALCULATE (
COUNTX ( SALES_EXPORT, [ORDER_DATE] ),
USERELATIONSHIP ( DATE_MASTER[Date], SALES_EXPORT[ORDER_DATE] ),
FILTER (
DATE_MASTER,
DATE_MASTER[Date] >= DATE ( 2019, 1, 1 ) && DATE_MASTER[Date] <= MAX ( DATE_MASTER[Date] )
)
)
)

 

I would like to calculative the cumulative count of sales by Female, using Mobile App, but picked up in the store. How do I tweak my measure to include GENDER = 'F' && CHANNEL = 'Mobile App' && 'SHIPPING_METHOD' = 'Store pick-up'?

 

Thanks in advance.

Need Help

3 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    6 years ago

    TD21 ,

     

    You may modify the measure like below:

    SALES_CUMULATIVE_COUNT :=
IF (
    MIN ( DATE_MASTER[Date] )
        <= CALCULATE ( MAX ( [ORDER_DATE] ), ALL ( SALES_EXPORT ) ),
    CALCULATE (
        COUNTX ( SALES_EXPORT, [ORDER_DATE] ),
        USERELATIONSHIP ( DATE_MASTER[Date], SALES_EXPORT[ORDER_DATE] ),
        FILTER (
            DATE_MASTER,
            DATE_MASTER[Date] >= DATE ( 2019, 1, 1 )
                && DATE_MASTER[Date] <= MAX ( DATE_MASTER[Date] )
                && DATE_MASTER[GENDER] = "F"
                && DATE_MASTER[CHANNEL] = "Mobile App"
                && DATE_MASTER[SHIPPING_METHOD] = "Store pick-up"
        )
    )
)

    Community Support Team _ Jimmy Tao

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

    • TD21's avatar
      TD21
      Helper II
      6 years ago

      v-yuta-msft 

       

      If you look at my original post, you'll see that [GENDER], [CHANNEL], and [SHIPPING_METHOD] are NOT in the DATE_MASTER table.

       

      Basically, I am look for ways to filter based on fields in 2 different tables:

      1. DATE_MASTER - [Date]

      2. SALES_EXPORT - [Gender], [Channel], [Shipping_Method]

       

      Thanks.

    • TD21's avatar
      TD21
      Helper II
      6 years ago

      v-yuta-msft 

       

      The post didn't help because you didn't read my question correctly. It CANNOT be marked as a Solution; that'd be confusing to other readers.