Maths equation on columns from multiple tables

[Toll]

Hello,

I wrote a long question before and it seems to have disappeared...

I am trying to perform the following equation:

( Table1[Revenue] / Table2[WDIC] ) * Table2[CWDSF] / Table3[Hours]

Table1 contains CustomerId and Revenue (whole number)

Table2 is a summarisation of a Calendar table with some extra calculated columns

Table3 contains CustomerId, DoneDate, and Hours (whole number)

Table4 is a Customers table with CustomerId and CustomerName

Table1 has a 1:1 Relationship with Table4

Calendar has a *:1 Relationship with Table2 (based on a column with "MMMM_YYYY")

Table3 has a *:1 Relationship with Table4 (based on CustomerId)

Table3 has a *:1 Relationship with Calendar (based on Table3[DoneDate] = Calendar[Date]) 

Would someone please be able to help me create the following output in a table visualisation? The result column:

CustomerId	Revenue	Sum of Hours	WDIC	CWDSF	Result
1	55000	600	21	12	52.38
2	30000	400	21	12	42.85
3	2000	29	21	12	3.94

This table has a filter so that it will always show the current month (i.e. September 2022)

The closest I got returned 100s of rows with each of the CustomerId duplicated multiple times and the result column was duplicated too.

[v-luwang-msft]

Hi @Toll ,

Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.

Best Regards
Lucien

[lbendlin]

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Toll]

Hi,

Thanks for your reply

Are you requesting a sample of all the tables? Otherwise, the table in the original question has the sample data and expected outcome.

[lbendlin]

Yes sample of all tables needed to recreate the issue.