Calculate how many based on 2 tables.

[Anonymous]

Hi.

A rookie question I hope someone can help me with.

I have 2 tables. 1 tables with categories and another table with same categories, but also with a number.

So, what I want to do is calculate how many of the categories in table 1 based on the number with same category in table 2.
Please look at my attach example of tables at the bottom.

As you can see in my example tables at the bottom I have Apples 3 times in table 1, and in table 2 an apple have the number 2. Then I need to multiply 3x2=6. Bananas is also 3 times, but the number there is 4, so then I need to multiply 3x4.
This I want to do with a measure and then summarize it all up in one KPI vizual.

Anyone who can help me with this?

Best regards
Marius

[Ashish_Mathur]

Hi,

From Table2, drag Ctegory to the visual.  Enter this calculated field formula

=SUMX(Table2,Table2[Number]*COUNTROWS(Table1))

Hope thish helps.

[TomMartens · Accepted solution]

Hey,

you can use DAX statement to create a measure in Table 2

Measure = 
SUMX(
	'Table2'
	,var currentCategory = 'Table2'[Category]
	return
	'Table2'[Amount] *
	COUNTROWS(
		FILTER('Table1'
			,'Table1'[Category] = currentCategory
		)
	)
)

Hope this is what you are looking for

Regards and have a nice weekend

Tom

[Anonymous]

Superb, exactly what I needed! Thank you so much.

Best regards

Marius