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
bargie
New Member

Matrix with category and sub category and another table

Hi,

 

New to PBI and hopefully this isnt a ridiculous question.

 

I have a working report that includes sales quantities/values etc in a matrix and it pulls a budgeted valued from a separate table.

 

I'm wanting to expand the matrix table to include a sub category. This works for the data directly in the sales table but the budgeted value is a showing as a total for all rows.

 

We have fact table of sales bridged to the budget table at the category level.

 

I've included an image below from a simplified verion of the report and I'm hoping to have the "Pen/Rollerball" budgeted quantity showing in the matrix as 84000 for example, next to the sales quantity.

bargie_2-1718984632702.png

bargie_1-1718984594945.png

 

I cant see where to attach a pbix, maybe as being a new user?

 

Thanks in advance!

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@bargie If the table is unrelated or it is related on something like Category, you could do something like this:

Budget Qty Measure = 
  VAR __Category = MAX('Table'[Category])
  VAR __SubCategory = MAX('Table'[Sub Category])
  VAR __Result = MAXX( FILTER( 'Other Table', [Category] = __Category && [Sub Category] = __SubCategory ), [Budget Qty] )
RETURN
  __Result

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
Super User

@bargie If the table is unrelated or it is related on something like Category, you could do something like this:

Budget Qty Measure = 
  VAR __Category = MAX('Table'[Category])
  VAR __SubCategory = MAX('Table'[Sub Category])
  VAR __Result = MAXX( FILTER( 'Other Table', [Category] = __Category && [Sub Category] = __SubCategory ), [Budget Qty] )
RETURN
  __Result

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler Thanks very much, that worked great!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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