EDIT: Correcting 'sample' data - the Record ID for Record Types "Campaign" and "Campaign by Placement" are identical for each Campaign entity; I originally entered each Record ID as unique.
I've searched around some, but unfortunately not quite sure how to even phrase it properly - so my apologies in advance if similar questions have already been asked and answered on the forums! As a little background, I am an intermediate Excel user, but a relative novice to PowerBi and DAX
Issue/Question:
We are receiving an excel file with marketing campaign settings data, and we need to use it to create a report that will allow a user to quickly determine the 'maximum' possible bid for a given keyword/campaign/placement. I'll reproduce the basic data structure below, along with our current attempt at a dax measure, and an example of what we are hoping to produce in a powerBi report.
Data structure:
| Record ID | Record Type | Campaign ID | Campaign Name | Bid | Keyword (string) | Strategy | Placement | Modifier |
| UniqueID 1 | Campaign | UniqueID 1 | Ad Campaign Name | | | Dyn Up | All | |
| UniqueID 1 | Campaign By Placement | UniqueID 1 | Ad Campaign Name | | | | TOS | 35% |
| UniqueID 1 | Campaign By Placement | UniqueID 1 | Ad Campaign Name | | | | ROS | |
| UniqueID 1 | Campaign By Placement | UniqueID 1 | Ad Campaign Name | | | | PDP | 0% |
| UniqueID 2 | Ad Group | UniqueID 1 | Ad Campaign Name | 1.25 | | | | |
| UniqueID 3 | Ad Product | UniqueID 1 | Ad Campaign Name | | | | | |
| UniqueID 4 | Keyword | UniqueID 1 | Ad Campaign Name | 4 | keyword 1 | | | |
| UniqueID 5 | Keyword | UniqueID 1 | Ad Campaign Name | 3.87 | keyword 2 | | | |
| UniqueID 6 | Keyword | UniqueID 1 | Ad Campaign Name | 2.87 | keyword 3 | | | |
With the above data, we want to calculate for every Record Type "Keyword" what our maximum possible bid is based on the values in the "Strategy" and "Modifier" columns. The calculation is essentially:
IF "Strategy" = "Dynamic Up" THEN (Bid+(Bid * Modifier) * 2), ELSE (Bid+(Bid * Modifier)
This needs to be performed for each value of the "Campaign By Placement" record for each "Campaign ID" - there will always be three (3) Campaign By Placement records for each unique "Campaign" record.
We have already made something similar to this in Excel, but for a variety of reasons this approach is not tenable as a permanent solution. As a demonstration, this is what we are producing in Excel:
(We have a roughly 1,000 campaigns to perform these calculations for, and the settings data will be updated weekly)
For Campaign "Ad Campaign Name" / Campaign ID "UniqueID 1"
| Strategy | TOS | PDP | ROS | |
| Dyn Up | 35% | 0% | 0% |
| Keyword | Bid | Max TOS | Max PDP | Max ROS |
| keyword 1 | $4.00 | $10.80 | $8.00 | $8.00 |
| keyword 2 | $3.87 | $10.45 | $7.74 | $7.74 |
| keyword 3 | $2.87 | $7.75 | $5.74 | $5.74 |
We tried creating the below measure in Powerbi to allow us to pull out the needed data (one for each value we needed extracted) - however it fails because we can't actually associate it back to the Keyword records.
FPTOS Mod =
CALCULATE(
SELECTEDVALUE( 'campaign_settings'[Modifier] ),
'campaign_settings'[Placement] = "TOS"
)
Any guidance or resources that anyone can share with us will be very appreciated!!
