Forum Discussion
SUM column based on two different filters for same unique ID
We record our promo codes for membership and our actual membership purchases as seperate line items in table call 'Activity'. There is no relationship between the two line items, except they're both recorded under the same member ID. Additionally, their member type is based on their latest 'Product_Code' purchased with a valid 'THRU_DATE'. I have some code that creates a measure to pull the MAX 'SEQN' for 'ACTIVITY_TYPE' DUES and DUESOFFER to ensure we're grabbing the most recent dues and dues offer transaction for a member. Sample data below:
| ID | LAST_NAME | FIRST_NAME | PRODUCT_CODE | ACTIVITY_TYPE | THRU_DATE | TRANSACTION_DATE | UF_1 | AMOUNT | SEQN | LatestDUESOFFER_SEQN | LatestDUES_SEQN |
| 9E+08 | Nonya | Business | ABC | DUESOFFER | 9/30/2021 0:00 | 9/20/2020 0:00 | Vgabf2020 | $0 | 1232405 | 1232405 | |
| 9E+08 | Nonya | Business | ABC-1YR | DUES | 9/30/2021 0:00 | 9/20/2020 0:00 | Approved | $49 | 1232404 | 1232404 | |
| 9E+08 | Nonya | Business | ABC-1YR | DUES | 8/31/2020 0:00 | 8/1/2019 0:00 | Approved | $43 | 1123159 | ||
| 9E+08 | Nonya | Business | ABC | DUESOFFER | 7/31/2019 0:00 | 7/6/2018 0:00 | WOODBOOK | $0 | 969559 | ||
| 9E+08 | Nonya | Business | ABC-1YR | DUES | 7/31/2019 0:00 | 7/6/2018 0:00 | Approved | $43 | 969557 |
My goal is to figure out total revenue for a given promotion code that we run. So expected results from sample data above is DuesOfferRevenue = $49. I tried something like this below but getting back no results.
DuesOfferRevenue =
VAR LatestDuesOfferID =
IF(NOT(ISBLANK('Activity'[LatestDUESOFFER_SEQN])), SELECTEDVALUE ( 'Activity'[ID] ), "NoDuesOffer")
VAR LatestDuesID =
IF(NOT(ISBLANK('Activity'[LatestDUES_SEQN])), SELECTEDVALUE ( 'Activity'[ID] ), "NoDues")
RETURN IF(LatestDuesID = LatestDuesOfferID, SUMX(Activity, Activity[AMOUNT]), BLANK())
Any guidance would be greatly appreciated.
Cheers,
Ryan
Not sure if my explanation was poor or it is a difficult problem issue to solve in PowerBI? But this was taking too long and there was a simplier route to a solution outside of PowerBI. My solution was create the Dues and DuesOffer relationship in SQL. Following SQL query gave me the DUES to DUESOFFER record relationship i was seeking and then imported into PowerBI using custom SQL query as 'DuesOffer2Dues' table. Then created a relationship in PowerBI model between 'DuesOffer2Dues' and 'Activity' table using the DuesOffer2Dues.D-SEQN = Activity.SEQN.
SELECT DUES.ID, DUES.ACTIVITY_TYPE AS 'D-ACTIVITY_TYPE', DUES.SEQN AS 'D-SEQN', DUESOFFER.ACTIVITY_TYPE AS 'DO-ACTIVITY_TYPE', DUESOFFER.SEQN AS 'DO-SEQN' FROM (SELECT ID, ACTIVITY_TYPE, MAX(SEQN) AS SEQN FROM Activity a WHERE ACTIVITY_TYPE = 'DUESOFFER' AND UF_1 = 'Vgabf2020' GROUP BY ID, ACTIVITY_TYPE ) AS DUESOFFER LEFT JOIN (SELECT ID, ACTIVITY_TYPE, MAX(SEQN) AS SEQN FROM Activity a WHERE ACTIVITY_TYPE = 'DUES' GROUP BY ID, ACTIVITY_TYPE ) AS DUES ON DUESOFFER.ID = DUES.ID
5 Replies
v-deddai1-msftCommunity Support
Hi Anonymous ,
Would you please explain more about the logic of 49 as total revenue for a given promotion code in your example. I failed to understand what's your need by your description.
Best Regards,
Dedmon Dai
Hi v-deddai1-msft ,
Apologies for missing mark first time around. I'll try to explain the $49 result better here. The data is not great, and there are two problems I need to solve. Problem 1) Identify the relevant "DUESOFFER" and "DUES" activity for each member ID. I've done this by creating a two measures. Each pulls the max SEQN number for each respective ("DUESOFFER" and "DUES") ACTIVITY_TYPE by ID that has a THRU_DATE > Today. These two measures are in the sample data below, LatestDUESOFFER_SEQN and LatestDUES_SEQN. Problem that I'm trying to solve in this post 2) Sum the AMOUNT for the latest "DUES" activity (LatestDUES_SEQN is "1232404" in sample data) where the corresponding LatestDUESOFFER_SEQN is not null and has a UF_1 = "Vgabf2020". I can't sum on the "DUESOFFER" record AMOUNT, because it's recorded as a $0 value. I need to sum on the LatestDUES_SEQN ("1232404" in the sample data) record AMOUNT. I've reordered the sample data with the SEQN, LatestDUESOFFER_SEQN and LatestDUES_SEQN as this might make more sense.
SEQN LatestDUESOFFER_SEQN LatestDUES_SEQN ID LAST_NAME FIRST_NAME PRODUCT_CODE ACTIVITY_TYPE THRU_DATE TRANSACTION_DATE UF_1 AMOUNT 1232405 1232405 900263805 Nonya Business ABC DUESOFFER 9/30/2021 0:00 9/20/2020 0:00 Vgabf2020 $0 1232404 1232404 900263805 Nonya Business ABC-1YR DUES 9/30/2021 0:00 9/20/2020 0:00 Approved $49 1123159 900263805 Nonya Business ABC-1YR DUES 8/31/2020 0:00 8/1/2019 0:00 Approved $43 969559 900263805 Nonya Business ABC DUESOFFER 7/31/2019 0:00 7/6/2018 0:00 WOODBOOK $0 969557 900263805 Nonya Business ABC-1YR DUES 7/31/2019 0:00 7/6/2018 0:00 Approved $43 - v-deddai1-msft
Hi Anonymous ，
Would you please try the below measure?
DuesOfferRevenue = VAR LatestDuesOfferID = IF(NOT(ISBLANK('Activity'[LatestDUESOFFER_SEQN])), SELECTEDVALUE ( 'Activity'[ID] ), "NoDuesOffer") VAR LatestDuesID = IF(NOT(ISBLANK('Activity'[LatestDUES_SEQN])), SELECTEDVALUE ( 'Activity'[ID] ), "NoDues") RETURN IF(LatestDuesID = LatestDuesOfferID, SUMX(FILTER(ALL(Activity),Activity[SEQN] = LatestDuesID), Activity[AMOUNT]), BLANK())If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai