Generating reports from unrelated tables

[VHosamane]

I have a RAW Table which contain employee timesheet entries for a project and client for each month. Later I have grouped this table so I can get total timesheet hours by client, project and month.

Then I have a second table, Ledger Table, which has the amount invoiced and cash received by Project, Client and per month. This is not aggregated. Detatils of the tables is given below.

I want to generate a report by Project, Client and per month with the following

- Total employees timesheet hours (InputHrs)

- Total amount invoiced (Invoiced)

- Total anount cashed that came in (CashIn)

Result report is also shown below (Result Output)

Can you please help with generating the report? Need help brining in the Invoiced and CashIn. When I am doing it it is taking the sum of all months and repeating the same for every month.

[v-juanli-msft]

Hi @VHosamane 

Is my method in your other post helpful?

https://community.powerbi.com/t5/Desktop/Showing-Grouped-amp-un-grouped-data-in-Matrix-report/td-p/7...

If not, please let me know.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

Why can't you create a combined key for both tables where you create a column that concatenates, for example client ID, project ID and month?

Outside of that, you should be able to accomplish this with measures by grabbing your client ID, project ID, etc from your table and then doing a FILTER on your other table and then a SUMX across that, for example.

[VHosamane]

Greg,

Thanks for your response.

I am new to Power BI & I am not clear how to move forward with what you said in your second paragraph. I was able to create a new column with ClientID, ProjectID and Month in both RAW and Ledger table. But needs help creating the measure which you mentioned in your 2nd paragraph.

It is summing all the CashIn and summing all the Invoiced irrespective of month and repeating the respective total for all the months. So it's not filtering by ClientID, ProjectID and Month.

So I need the syntax to create a measure which will give me CashIn and Invoiced based on ClientID, ProjectID and Month. 

Thanks.