Forum Discussion

datamodel's avatar
datamodel
Icon for Advocate I rankAdvocate I
6 years ago

Crossjoin, GENERATE?

This is probably simple, but I just can't seem to figure it out. Need a way to count all Activity Type = 8 by product by customer. In the activity below, both 888 and 999 products should get a cou...
Need Help
datamodel's avatar
datamodel
Icon for Advocate I rankAdvocate I
6 years ago

Thank you, that helped. I modified the calc a bit to add it to a measure, instead of a new table. The below calc is able to get the correct total but the only issue is that it doesn't break it down by Product on the product dimension. It does however break it down by Customer.

 

ActivityCount = SUMX(
    GROUPBY (
        NATURALLEFTOUTERJOIN(
            CALCULATETABLE(SUMMARIZE('Activity Table','Activity Table'[PRODUCT_ID],'Activity Table'[CUSTOMER_ID]),NOT(ISBLANK('Activity Table'[PRODUCT_ID]))),
            SUMMARIZE('Activity Table','Activity Table'[CUSTOMER_ID],"activityCount",CALCULATE(COUNTROWS('Activity Table'),'Activity Table'[ACTIVITY_TYPE]=8))
            ),
    'Activity Table'[CUSTOMER_ID],
    "AveragePerCustomer", MAXX ( CURRENTGROUP (), [activityCount] )
    ),
    [AveragePerCustomer]
)
PRODUCTActivityCount
888(missing value)
777(missing value)
999(missing value)
Total7
  
CUSTOMERActivityCount
1233
45672
789002
Total7
datamodel's avatar
datamodel
Icon for Advocate I rankAdvocate I
6 years ago

Figured it out by summarizing ALLSELECTED activity. Thank you jdbuchanan71 for the 90% :)

 

ActivityCount = SUMX(
    GROUPBY (
        NATURALLEFTOUTERJOIN(
            CALCULATETABLE(SUMMARIZE('Activity Table','Activity Table'[PRODUCT_ID],'Activity Table'[CUSTOMER_ID]),NOT(ISBLANK('Activity Table'[PRODUCT_ID]))),
            SUMMARIZE(ALLSELECTED('Activity Table'),'Activity Table'[CUSTOMER_ID],"activityCount",CALCULATE(COUNTROWS('Activity Table'),'Activity Table'[ACTIVITY_TYPE]=8))
            ),
    'Activity Table'[CUSTOMER_ID],
    "AveragePerCustomer", MAXX ( CURRENTGROUP (), [activityCount] )
    ),
    [AveragePerCustomer]
)