Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Calculate how many based on 2 tables.

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

 

Need Help
  • TomMartens's avatar
    TomMartens
    8 years ago

    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

     

3 Replies

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

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

       

      Best regards

      Marius

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    8 years ago

    Hi,

     

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

     

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

    Hope thish helps.