The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
I have a measure that depending on campaign type calls on a subset measure to show how many customers joined the campaign.
My issue is that when I try and do a grand total it returns "Blank" since a campaign has a date range its active and each campaign is different. If I filter to a single campaign it works but I need a total value for all campaigns.
link to the tet file
https://www.dropbox.com/s/0c110ri0wx02p53/Marketing%20Campaigns%20Dashboard%20%283%29.pbix?dl=0
Below is the measure that looks at the campaign type and calls on the appropriate measure afterwards. You can also see it's values in the right side of the picture.
Total New Customers =
if(SELECTEDVALUE(Campaign[Campaign_Reason__c]) = "Cross sell – Upgrade Internet", [New Gig Customers],
IF( SELECTEDVALUE(Campaign[Campaign_Reason__c]) = "Cross sell – EV Rate", [New EV Rate Customers],
if(SELECTEDVALUE(Campaign[Campaign_Reason__c]) = "Cross sell – TOU rate", [New TOU Rate Customers],
IF(SELECTEDVALUE(Campaign[Campaign_Reason__c]) = "Acquisition – Residential", [New Go Customers],
IF(SELECTEDVALUE(Campaign[Campaign_Reason__c]) = "Cross sell – Bank Draft", [New Bank Draft Customers],
IF(SELECTEDVALUE(Campaign[Campaign_Reason__c]) = "Cross sell – Paperless", [New Paperless Customers],
"")
)))))
Example of one of the sub measures. All more or less follow the same logic. The action needs to take place between the campaign's dates and a certain condition has to be met.
New Gig Customers =
CALCULATE( DISTINCTCOUNT(Charges[Charge Cust#]),
ALLEXCEPT(Charges,Charges[Charge Cust#]),
Charges[CHG_DATE] >= SELECTEDVALUE(Campaign[StartDate]),
Charges[CHG_DATE] <= SELECTEDVALUE(Campaign[EndDate]),
Charges[CHG_DESC] IN {"Gigabit 1000", "Gigabit 1000 OK"},
Campaign[Campaign_Reason__c] = "Cross sell – Upgrade Internet",
Charges[ACTION_FLAG] = "START")
I've tried using SUMX but to this point it wasn't successful. The ultimate goal would be to show a grand total of all of the "Total New Customers" values.
Any help is appreciated and if you need additional details please let me know.
Solved! Go to Solution.
@GunnerJ , You can try a new measure like
Total New Customers =
Sumx(Values(Campaign[Campaign_Reason__c]) , calculate( if(SELECTEDVALUE(Campaign[Campaign_Reason__c]) = "Cross sell – Upgrade Internet", [New Gig Customers],
IF( SELECTEDVALUE(Campaign[Campaign_Reason__c]) = "Cross sell – EV Rate", [New EV Rate Customers],
if(SELECTEDVALUE(Campaign[Campaign_Reason__c]) = "Cross sell – TOU rate", [New TOU Rate Customers],
IF(SELECTEDVALUE(Campaign[Campaign_Reason__c]) = "Acquisition – Residential", [New Go Customers],
IF(SELECTEDVALUE(Campaign[Campaign_Reason__c]) = "Cross sell – Bank Draft", [New Bank Draft Customers],
IF(SELECTEDVALUE(Campaign[Campaign_Reason__c]) = "Cross sell – Paperless", [New Paperless Customers],
"")
)))))) )
@GunnerJ , You can try a new measure like
Total New Customers =
Sumx(Values(Campaign[Campaign_Reason__c]) , calculate( if(SELECTEDVALUE(Campaign[Campaign_Reason__c]) = "Cross sell – Upgrade Internet", [New Gig Customers],
IF( SELECTEDVALUE(Campaign[Campaign_Reason__c]) = "Cross sell – EV Rate", [New EV Rate Customers],
if(SELECTEDVALUE(Campaign[Campaign_Reason__c]) = "Cross sell – TOU rate", [New TOU Rate Customers],
IF(SELECTEDVALUE(Campaign[Campaign_Reason__c]) = "Acquisition – Residential", [New Go Customers],
IF(SELECTEDVALUE(Campaign[Campaign_Reason__c]) = "Cross sell – Bank Draft", [New Bank Draft Customers],
IF(SELECTEDVALUE(Campaign[Campaign_Reason__c]) = "Cross sell – Paperless", [New Paperless Customers],
"")
)))))) )