Related Column not showing up while creating a calculated field

[senthil9324]

Hi Guys,

As show below I have 2 table and the relationship is also defined.

when I try to write a calculated field using Active weekly field of table "Weekly Active Dealers", the column is not showing up and its throwing error.

Could some one help me if some thing is wrong with below model.

Thanks in advance.

Senthil.

[v-cherch-msft · Accepted solution]

Hi @senthil9324

You may try to use below measure.

Measure =
CALCULATE ( COUNT ( 'Opportunity'[Order Created Time] ) )
    / SELECTEDVALUE ( ' Weekly active dealers'[Active Weekly] )

Regards,

Cherie

[jthomson]

Post some data and what you're expecting the result to be, the formula you list makes little sense and the data model isn't helping either

[senthil9324]

Hi Tomson,

I am trying to calculate weekly average orders by dealer.

Note: Not all dealers get orders on weekly basis, so need to consider only count of distinct dealers who got order in that week.

Opportunity table:

Order Created Time	Week Range	Dealer	Segment
7/25/2018 15:47	07/20/2018 - 07/26/2018	YC PowerSports Columbia	Powersports
7/23/2018 20:48	07/20/2018 - 07/26/2018	Bright Powersports	Powersports
7/23/2018 15:25	07/20/2018 - 07/26/2018	Bright Powersports	Powersports
7/22/2018 1:44	07/20/2018 - 07/26/2018	YC PowerSports Columbia	Other
7/20/2018 16:41	07/20/2018 - 07/26/2018	Love Motorsports	Boats
7/20/2018 15:24	07/20/2018 - 07/26/2018	Love Motorsports	Boats
7/19/2018 14:54	07/13/2018 - 07/19/2018	YC PowerSports Columbia	Other
7/19/2018 14:54	07/13/2018 - 07/19/2018	Love Motorsports	Boats
7/18/2018 9:56	07/13/2018 - 07/19/2018	Wheels in Motion	Powersports

Weekly active dealers: (created this based on Opportunity table: 

Week Range	Active Weekly
07/20/2018 - 07/26/2018	139
07/13/2018 - 07/19/2018	124

Please let me know if you need any other information.

[Anonymous]

The syntax for the RELATED is

RELATED ( 'Weekly active dealers'[Active Weekly]). 

Can you try this?

Thanks

Raj

[senthil9324]

Hi Raj,

Tried, but no sucess.

Below is the error messsage.

Am not sure what is the exact problem, is this something to do with Many to one relationship model.

Thanks,

Senthil

[Anonymous]

I think you are using the RELATED() in measure, which wont work.

Can you split the activities:

1. Create calculated column with Calc Column=RELATED() function.

2. Create second column with the full formula, where you use Calc Column.

Thanks

Raj