Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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
Microsoft Employee
Microsoft 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
Microsoft Employee
Microsoft 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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.