Creating a column based on the conditions of another (Figure this out you're a legend)

[Ttaylor9870]

Hi All,

Here is the 2 tables I am working with...

Sales Table

Owner (Spec)	Owner( Housebuilder)	Owner(Commercial)	Owner(NAM)	Region	Est Revenue	Est. Spec Value
	Gary Garyson			Admin	£100,000	£245,000
		Sam Samson		Admin	£174,000	£290,000
		Sam Samson	Richard Richardson	Scotland	£155,000	£212,000
	James Jameson	Sam Samson	Richard Richardson	ROI	£109,000	£288,000
	John Johnson		Richard Richardson	Admin	£112,000	£245,000

Now here is my other table...

Owner & Region

Owner	Region
Gary Garyson	Midlands
James Jameson	ROI
John Johnson	NI
Sam Samson	Scotland

My problem is that some of my sales records in my sales table have the value "Admin" assigned to them in the Region column. I would like a solution on how would I go about assigning the correct Est. Revenue to the correct region? My catch is that I would like the Est. Revenue assigned to the owners region in this order... Owner (Housebuilder) if this is empty then then Owner (Commercial) and if this is empty then finally Owner (NAM).

Also this is only a small example of my Sales table and this all assumes that Owner(Spec) has been left blank. Most rows do infact have the Owner(Spec) column with a value in it. I want this solution to work in a way where even if there is Owner(Spec) values in the column aslong as Admin is the region completely disregard this column.

Maybe I need a new region column created to show the correct region in the order of assgment I would like?

Any help would be greatly appreciated! 🙂

[Ttaylor9870]

@v-xiaosun-msft Thank you so much you're class!!! Many many thanks! 🙂

[v-xiaosun-msft · Accepted solution]

Hi @Ttaylor9870 ,

According to your deescription, here is my solution.

I created two samples, one is that Owner(Spec) has been left blank and the other one is that Owner(Spec) has values.

Create a column to show the which owner the Est. Revenue will be assigned to according to your order.

Column =
IF (
    'Sales Table'[Region] = "Admin",
    IF (
        'Sales Table'[Owner( Housebuilder)] <> BLANK (),
        'Sales Table'[Owner( Housebuilder)],
        IF (
            'Sales Table'[Owner(Commercial)] <> BLANK (),
            'Sales Table'[Owner(Commercial)],
            'Sales Table'[Owner(NAM)]
        )
    ),
    IF (
        'Sales Table'[Owner (Spec)] <> BLANK (),
        'Sales Table'[Owner (Spec)],
        IF (
            'Sales Table'[Owner( Housebuilder)] <> BLANK (),
            'Sales Table'[Owner( Housebuilder)],
            IF (
                'Sales Table'[Owner(Commercial)] <> BLANK (),
                'Sales Table'[Owner(Commercial)],
                'Sales Table'[Owner(NAM)]
            )
        )
    )
)

You need to establish a relationship between two tables.

And then create a new column to return the region.

Region2 =
CALCULATE (
    VALUES ( 'Owner & Region'[Region] ),
    FILTER ( 'Owner & Region', 'Sales Table'[Column] = 'Owner & Region'[Owner] )
)

Final output:

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.