Skip to main content
cancel
Showing results for 
Search instead 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

Reply
connork
Helper I
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
Phil_Seamark
Employee
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

 

matrix.png

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

2 REPLIES 2
Phil_Seamark
Employee
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

 

matrix.png

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi @Phil_Seamark,

 

Exactly what I needed - thanks!

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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