cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Avg per Day by Month & YTD in Matrix

Tables

Say I have 2 tables, one with monthly totals by customer and one that has the working days per month (see below).

Goal

Setup a matrix table that shows the average hours per customer per day by month and YTD.

Issue

I can't get the grand total of the rows to calculate correctly by customer and in total.

Any ideas on how to get this to work in a matrix in PBI?  Just looking for concept of how to apporach this issue (if it is possible).

Thanks

1 ACCEPTED SOLUTION
Employee

Hi @connork

Please give this calculated measure a crack and let me know how you go

```Measure =
IF(
ISFILTERED('TABLE2 - WORK DAYS'[Month]),
DIVIDE(
SUM('TABLE1-DATA'[Hours]),
MAX('TABLE2 - WORK DAYS'[Working Days])
),
------ Grand Total Calc Here ------
DIVIDE(
CALCULATE(SUM('TABLE1-DATA'[Hours])),
CALCULATE(SUM('TABLE2 - WORK DAYS'[Working Days])
)
)
)```

Here is the result I got

Proud to be a Datanaut!

2 REPLIES 2
Employee

Hi @connork

Please give this calculated measure a crack and let me know how you go

```Measure =
IF(
ISFILTERED('TABLE2 - WORK DAYS'[Month]),
DIVIDE(
SUM('TABLE1-DATA'[Hours]),
MAX('TABLE2 - WORK DAYS'[Working Days])
),
------ Grand Total Calc Here ------
DIVIDE(
CALCULATE(SUM('TABLE1-DATA'[Hours])),
CALCULATE(SUM('TABLE2 - WORK DAYS'[Working Days])
)
)
)```

Here is the result I got

Proud to be a Datanaut!

Helper I

Exactly what I needed - thanks!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors