Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Calculating Value taking data from different tables on condition

I have data from two tables that I want to mix in a matrix column, it only takes information from one table per row, a central table with a condition decides which table to take the data from (Picture Below)

 

 

I have three tables that are used to calculate the hourly sale rate of an employee.

 

Table 1 : Fixed Rate

This table contains the employee  and a fixed hourly sale rate

 

Table 2: Hourly rate per package

The hours of this employee are sold in packages and each package has an amount of hours and it can have a different rate per package

 

Table 3: Central Table

This tables contains the employee and which kind of hourly rate to apply

 

Table 4 : Employee Table ( Contains only employee data)

 

I have calculated two measures in order to calculate the average hourly sale rate, one for table 1 and one for table 2 . I want to use a measure that takes data from the calculated measure for table 1 when the employee belongs to the fixed hourly rate table, and from  measure 2 when the employee belongs to the hours sold in packages ( table 2).

 

 

 

Is it possible to achieve something like this?

 

Thank you

2 Replies

  • v-cazheng-msft's avatar
    v-cazheng-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous 

     

    I roughly get your point. Is it convenit for you to provide some sample data so we can do research based on them to help you find the solution? Thanks in advance!

     

    Best Regards,

    Community Support Team _Caiyun Zheng

  • technolog's avatar
    technolog
    Icon for Super User rankSuper User
    11 months ago

    First, create two base measures that return blank when there are no rows in their respective tables.

    Example:

    Avg Fixed Rate =
    VAR rows = CALCULATE(COUNTROWS(fixed_hourly_rate))
    RETURN
    IF(rows = 0, BLANK(), AVERAGEX(fixed_hourly_rate, fixed_hourly_rate[hourly_rate]))

    Avg Package Rate =
    VAR rows = CALCULATE(COUNTROWS(package_hourly_rate))
    RETURN
    IF(rows = 0, BLANK(), DIVIDE(SUM(package_hourly_rate[amount]), SUM(package_hourly_rate[hours])))

    Then create the combined measure that takes the first non-blank result.

    Hourly Sale Rate =
    COALESCE([Avg Fixed Rate], [Avg Package Rate])

    This way, each employee row in the matrix will show whichever rate exists for that employee.

    If you need correct totals (for example an average across all employees), use this version instead:

    Hourly Sale Rate Total =
    VAR t =
    ADDCOLUMNS(
    VALUES(employee_category[employee_id]),
    "rate", COALESCE([Avg Fixed Rate], [Avg Package Rate])
    )
    RETURN
    AVERAGEX(t, [rate])

    If your central table contains a field specifying which type of rate applies, you can use this logic instead:

    Hourly Sale Rate =
    VAR src=SELECTEDVALUE(employee_category[rate_source])
    RETURN
    SWITCH(src,
    "Fixed", [Avg Fixed Rate],
    "Package", [Avg Package Rate],
    BLANK()
    )

    Make sure relationships flow correctly from employee_category to both rate tables so that the filter context works as expected. If any relationship is inactive, activate it with USERELATIONSHIP inside your base measures.