Calculate value based on different collumn

[peaky]

Hello, 

I've two tables A and B as below. I would like to SUM values from table A based on category and divide them by table B based on other category. Is that possible?

Main goal is to: sum VALUE from table A where category = "A" and divide it by sum of Value from table B where Description = "In Storage". Is it possible? I've tried to use IF, SWITCH, CALCULATE and also separate collumns on 'Storage Costs' and 'Other costs' based on category but it didn't help.

Here is my function

CALC =

Switch(TRUE(),

'Table A'[Category] = "A", SUM('Table A'[Storage Costs])/Calculate(SUM('Table B'[Value]'),'Table B'[Description] = "In Storage"), 

SUM('Table A'[Other Costs])/Calculate(SUM('Table B'[Value]'),'Table B'[Description] = "Added to storage")

but when I use function above error occures:

A single value for column 'Category' in table 'Table A' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

I think it's important to mention that those 2 tables are connected witch each other by relationships on 5 collumns like months, years, entities, scenarios and business lines. I've created additional 5 relationship tables so that I can filter them both on those 5 dimensions.

Table A

Month	Category	Value	Storage Costs	Other costs
JAN	A	10	10	0
JAN	B	20	0	20
JAN	C	30	0	30
JAN	A	15	15	0
JAN	A	25	25	0
JAN	C	35	0	35
JAN	A	11	11	0
JAN	B	12	0	12
JAN	A	13	12	0

Table B

MONTH	Dimension	Description	Value
JAN	TX	Added to storage	40
JAN	TX	In Storage	5000
JAN	FX	Added to storage	50
JAN	FX	Added to storage	60
JAN	TX	Added to storage	70
JAN	FX	In Storage	2000
JAN	HX	In Storage	3000
JAN	YX	In Storage	4000
JAN	IX	In Storage	6000

[johnt75]

Try

My Measure =
VAR tableAValue =
    CALCULATE ( SUM ( 'Table A'[Value] ), 'Table A'[Category] = "A" )
VAR tableBValue =
    CALCULATE ( SUM ( 'Table B'[Value] ), 'Table B'[Description] = "In Storage" )
RETURN
    DIVIDE ( tableAValue, tableBValue )

[peaky]

And how I can inclued those 2 variances in 1 measure? I mean I want to have in one measure divided category A by In storage and category B or C by Added to Storage.

[johnt75]

It depends on how the measure will be displayed. If you're going to be including the category in the visual, then you can use SELECTEDVALUE to determine which category is in context, but if you're not going to have the category then you would need to use an iterator to introduce that context

[peaky]

Okay and is there a way to achieve this using switch(true()) function? Every time the error occures about determining single value in collumn.