Using Sumx with USERELATIONSHIP

[Anonymous]

Hello, 

I'm tring to calculate the following logic (but I get an error: USERELATIONSHIP function can only be used in the CALCULATE function.)

T today =

CALCULATE(SUMX(vwbi_target_actual_sales,vwbi_target_actual_sales[Gap_From _Target] / USERELATIONSHIP(vwbi_target_calls[No_Of_Deliveries_Remaining_For_Current_Month],vwbi_target_actual_sales[Gap_From _Target]))

)

So I tried this syntax as well: 

CALCULATE(SUMX(vwbi_target_actual_sales,vwbi_target_actual_sales[Gap_From _Target] / CALCULATE(USERELATIONSHIP(vwbi_target_calls[No_Of_Deliveries_Remaining_For_Current_Month],vwbi_target_actual_sales[Gap_From _Target]))

))

But it didn't work too. 
I use sumx function because i need to provide this calculation at a row level.

In the end I need to divide vwbi_target_actual_sales[Gap_From _Target] by vwbi_target_calls[No_Of_Deliveries_Remaining_For_Current_Month] and sum the results at a row level.

Can anayone explain what is the problem in my query, and how can i solve it? 

[TravisBartel]

This is an old thread. But providing an answer for future people.

I think I have the solution; well, it worked for my, anyways.

You are correct to use CALCULATE... sort of. Trick is to use CALCULATETABLE. As I understand CALCULATETABLE it's that it can essentially do everything that CALCULATE can do, except it applies it to a table. Hence the name 🙂 Therefore, if you wrap your table in a CALCULATETABLE formula, and the employ the USERELATIONSHIP within that, then it should work. (Again, worked for me, so hopefully it works for all)

So, I think if you had changed your measure to the following, it would have worked:

SUMX(

CALCULATETABLE(

vwbi_target_actual_sales,

USERELATIONSHIP(

vwbi_target_calls[No_Of_Deliveries_Remaining_For_Current_Month],

vwbi_target_actual_sales[Gap_From _Target]

)

),

vwbi_target_actual_sales[Gap_From _Target]

)

[amitchandak]

@Anonymous , You need this ?

divide(sum(vwbi_target_actual_sales[Gap_From _Target]), calculate(sum(vwbi_target_actual_sales[Gap_From _Target]), USERELATIONSHIP(vwbi_target_calls[No_Of_Deliveries_Remaining_For_Current_Month],vwbi_target_actual_sales[Gap_From _Target])))

Or?

calculate(sum(vwbi_target_actual_sales[Gap_From _Target]), USERELATIONSHIP(vwbi_target_calls[No_Of_Deliveries_Remaining_For_Current_Month],vwbi_target_actual_sales[Gap_From _Target]))

[Anonymous]

Hi amitchandak,

Your first query is what I need but I got an error after executing it:
"USERELATIONSHIP function can only use the two columns references participating in relationship."

I'll try to clearify what I want to calculate: 

I have this table:

I need to calculate the Dailty target column = gap from target / No of remaining deliveries.

gap from target is a column exsiting in table A and No of remaining deliveries is a column existing in table 2. 

As you can see in the image the green marked row is at employee level and the red rows are at customer level (each employee has several customers). 

What I want is to sum all customers rows and to get those results at the employee level.

For example to sum - 22,186+19,960+18,017...... 

Right now I dont get those results  - It takes the total gap result and divieds by total remaining deliveries. 

This is why i need to create a calculation at a row level. 

[amitchandak]

@Anonymous , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

refer this how to use

HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970