How Can I make measure SUM my amount according to level and parent category? using power bi

Question

I have two tables in power bi, the Invoices table and the Categories table. In the Invoices table, I have a column for CategID and a column for sales amount. In the Categories table, I have columns for ID , Name, level, and parentID. In the Categories table, there are main categories with level 2, such as:

Name	Level	ID	ParentID
food	2	0012	0
Games	2	0013	0

etc...

And the subcategories under them have level 3 and are linked to the level 2 categories through the parent ID. For example:

Name	Level	ID	ParentID
tomato	3	001201	0012
cake	3	001202	0012
fish	3	001203	0012
kids games	3	001301	0013
electronic games	3	001302	0013

the Main category and subcategory in same table.

The two tables are linked by the (ID) column in the Categories table and the CategID column in the Invoices table. I would like to calculate the sales amount for each of the categories 'food' and 'Games' and etc.. from the Invoices table.

keep in mind "the name of category and subcategory are changing depending on customer"

Answer 1

Create a Measure with:

Category L2 Sales amount = 
  CALCULATE(
    SUM( 'Invoices'[Sales amount] ),
    ALL(Categories),
    Categories[ParentID] IN DISTINCT(Categories[ID])
  )