Calculate a Measure based on list of names in another table.

[patrickrw99]

Hi,

I know this may seem simple but is there any way to calculate an already existing measure with a filter based on another table?

I have 1 main table with 2 columns (types of Vehicles and 'Value 1' as a value for each vehicle) , with 3 accompanying tables (List A, List B, List C) in which each table contain a random list of the types of vehicles from the main table pool.

I would like to create a measure which is able to calculate the measure of the total sum of 'Value 1' for each list, showing the total of Value 1 for the types of vehicle that is listed in each list.

To Calculate the total sum of a'Value 1' is easy enough, I'm using the formula : CALCULATE (sum(Value 1),ALL(Vehicles)), However, I'm asking for help for the measure formula to calculate the total asum of 'Value 1', for instance, for each List A, List B, etc.

Please find the attached sample excel file as an explanation of the above.

https://docs.google.com/spreadsheets/d/1inXO-3bAK91QQg2R06kjninKlmFygQJX/edit?usp=sharing&ouid=11356...

Any help is appreciated, thank you!

[amitchandak · Accepted solution]

@patrickrw99 , Assume you have column where we can use this list A from another table

CALCULATE (sum(Value 1),filter(Vehicles,Vehicles[Type] in values(Table2[List A] )))

of refer Treatas

https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/

[patrickrw99]

Hi,

Thank you for the answer!

I would like to ask a follow up question, is it possible that if the lookup table (List A, List B, List C) have another column ('Input Month'), to further filter by this column? therefore, for example: the measure only calculate the Vehicles in List A which have the 'Input Month' Column value of "January"?