Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
GunnerJ
Post Patron
Post Patron

How to sum a grand total where measure is dependent on dates

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],
       "")
       )))))

GunnerJ_0-1666126561533.png

 

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. 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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],
"")
)))))) )

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

@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],
"")
)))))) )

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors