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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
meimeilili35
New Member

Help On Calculation

Hello, I am a new PowerBI user. I tried to figure out how to convert one of tableau calculation to Power BI.

I want to calculate spend based on different formula if Program Name contain certain keywords. Program Name is a column with string value and Query1 is the table name. 

 

Below is the measure I created, there is no syntax error but I got wrong result. I tried to change sumx to sum, but I was given an syntax error. Can anyone help to point out what I did wrong?

 

Spend V2 =
sumx(Query1,
SWITCH(
TRUE(),
CONTAINSSTRING(Query1[Program Name], "BT"),Query1[DCM_Impressions] * 24/1000,
CONTAINSSTRING(Query1[Program Name], "Endemic"),Query1[DCM_Impressions] * 50/1000,
CONTAINSSTRING(Query1[Program Name], "Comprehensive"),Query1[DCM_Impressions] * 90/1000,
CONTAINSSTRING(Query1[Program Name], "Retargeting") && Query1[report_date] >= Date(2022,07,01),Query1[DCM_Impressions] * 8/1000

)

5 REPLIES 5
meimeilili35
New Member

Hi Jihwan_Kim, is there any workaround to avoid the error I encountered? Thanks

meimeilili35
New Member

Hi
 
 
 
I am not sure how to insert an excel file here. Here is a sample data.
DateProgram NameDCM Impressions
1/1/2023Chevy LMA Blazer Awareness BT10000
1/2/2023Chevy LMA Blazer Awareness Endemic20000
1/5/2023Chevy LMA Blazer Conquest BT50000
1/6/2023Chevy LMA Blazer Conquest Endemic60000
1/10/2023Chevy LMA Blazer Retargeting100000
1/12/2023Chevy LMA Blazer Retention BT120000
1/13/2023Chevy LMA Blazer Awareness BT30000
1/14/2023Chevy LMA Blazer Awareness Endemic40000
1/17/2023Chevy LMA Blazer Conquest BT70000
1/18/2023Chevy LMA Blazer Conquest Endemic80000
1/22/2023Chevy LMA Blazer Retargeting120000
1/24/2023Chevy LMA Blazer Retention BT140000

 

I did pivot, my expect result should be below. Retail Spend Measure is calculated field based on the formula.

Program NameDCM ImpressionRetail Spend Measure
Chevy LMA Blazer Awareness BT40000$960
Chevy LMA Blazer Awareness Endemic60000$3,000
Chevy LMA Blazer Conquest BT120000$2,880
Chevy LMA Blazer Conquest Endemic140000$7,000
Chevy LMA Blazer Retargeting220000$1,100
Chevy LMA Blazer Retention BT260000$6,240
FreemanZ
Super User
Super User

hi @meimeilili35 

the code seems ok. could you also post some sample data?

Jihwan_Kim
Super User
Super User

Hi,

I am not sure how your datamodel looks like, but please try the below if it works.

 

Spend V2 measure =
SUMX (
    Query1,
    CALCULATE (
        SWITCH (
            TRUE (),
            CONTAINSSTRING ( Query1[Program Name], "BT" ),
                Query1[DCM_Impressions] * 24 / 1000,
            CONTAINSSTRING ( Query1[Program Name], "Endemic" ),
                Query1[DCM_Impressions] * 50 / 1000,
            CONTAINSSTRING ( Query1[Program Name], "Comprehensive" ),
                Query1[DCM_Impressions] * 90 / 1000,
            CONTAINSSTRING ( Query1[Program Name], "Retargeting" )
                && Query1[report_date] >= DATE ( 2022, 07, 01 ),
                Query1[DCM_Impressions] * 8 / 1000
        )
    )
)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

Thanks  Jihwan_Kim,

 

I tried to use the solution you provided, I got an error message.

 

"A single value for column 'Program Name' in table 'Query1' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result"

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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