Matrix with category and sub category and another table

[bargie]

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.

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

Thanks in advance!

[Greg_Deckler · Accepted solution]

@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

[Greg_Deckler · Accepted solution]

@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

[bargie · Accepted solution]

@Greg_Deckler Thanks very much, that worked great!