Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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! 🙂
Solved! Go to 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.
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.
User | Count |
---|---|
42 | |
27 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |