Help On Calculation

[meimeilili35]

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

)

[meimeilili35]

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

[meimeilili35]

Hi

FreemanZ,

I am not sure how to insert an excel file here. Here is a sample data.

Date	Program Name	DCM Impressions
1/1/2023	Chevy LMA Blazer Awareness BT	10000
1/2/2023	Chevy LMA Blazer Awareness Endemic	20000
1/5/2023	Chevy LMA Blazer Conquest BT	50000
1/6/2023	Chevy LMA Blazer Conquest Endemic	60000
1/10/2023	Chevy LMA Blazer Retargeting	100000
1/12/2023	Chevy LMA Blazer Retention BT	120000
1/13/2023	Chevy LMA Blazer Awareness BT	30000
1/14/2023	Chevy LMA Blazer Awareness Endemic	40000
1/17/2023	Chevy LMA Blazer Conquest BT	70000
1/18/2023	Chevy LMA Blazer Conquest Endemic	80000
1/22/2023	Chevy LMA Blazer Retargeting	120000
1/24/2023	Chevy LMA Blazer Retention BT	140000

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

Program Name	DCM Impression	Retail Spend Measure
Chevy LMA Blazer Awareness BT	40000	$960
Chevy LMA Blazer Awareness Endemic	60000	$3,000
Chevy LMA Blazer Conquest BT	120000	$2,880
Chevy LMA Blazer Conquest Endemic	140000	$7,000
Chevy LMA Blazer Retargeting	220000	$1,100
Chevy LMA Blazer Retention BT	260000	$6,240

[FreemanZ]

hi @meimeilili35 

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

[Jihwan_Kim]

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
        )
    )
)

[meimeilili35]

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"