Showing Grouped & un-grouped data in Matrix report

[VHosamane]

I have two tables, one is grouped by month with the data grain at a day level and other un-grouped table where the data grain is at month level. I want the matrix report to display values from both these tables.

The grouped table contains staff daily timesheet entries. They include clientid, projectid, input hours, timesheet date, timesheet month, hourly rate, etc. This table I have grouped summing the input hours by month, summing the rate times input hours which gives me the cost by month. So I have grouped table

- ClientID

-ProjectID

-TimesheetMonth

-Sum of InputHrs by month (MonthlyInputHrs)

-Rate times InoutHrs by month (MonthlyCost)

The un-grouped table provides income by Client, Project and month. So this table has

-ClientID

-ProjectID

-Month

-Income

In my matrix report, I want to show the details as follows:

I have tried doing a combined key by ClientID, ProjectID and Month and joining them, but with no luck. When I join them Income gets summed up and get higher value than what needs to be. I also tried creating a measure and was unsuccessful as I am newbie in Power BI.

I am a newbie to Power BI and need some help.

[v-juanli-msft · Accepted solution]

Hi @VHosamane 

Assume you have data as below(sheet1-grouped table, sheet2-ungrouped table)

Please create a new table with this formula

sumarized table =
SELECTCOLUMNS (
    SUMMARIZE (
        Sheet1,
        Sheet1[client id],
        Sheet1[project],
        Sheet1[timesheet month],
        Sheet1[hourly rate],
        "monthly hours", SUM ( Sheet1[input hours] )
    ),
    "client id", [client id],
    "project", [project],
    "month", [timesheet month],
    "monthly input hours", [monthly hours],
    "monthly cost", [monthly hours] * [hourly rate]
)

Then add a column into this table

lookup income =
LOOKUPVALUE (
    Sheet2[imcome],
    Sheet2[client id], 'sumarized table'[client id],
    Sheet2[project], 'sumarized table'[project],
    Sheet2[month], 'sumarized table'[month]
)

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.

[VHosamane]

Thank you Maggie. I am still working on your proposed solution and will post my feedback.