Conditional calculation based on filter value selected

[klehar]

Hi, 

Hi,

I have 4 tables in my dataset (stripping the dataset for simplicity)

Orders Fact Table : Lease and Order Value are my desired output columns in power bi)

Fiscal Quarter	Geo	Amount	Merge	Lease	Order Value
FY21Q1	India	100	FY21Q1India	0.5	50
FY21Q2	India	200	FY21Q2India	0.5	100
FY21Q3	India	300	FY21Q3India	0.5	150
FY21Q4	India	400	FY21Q4India	0.5	200
FY21Q1	Japan	1000	FY21Q1Japan	0.1	100
FY21Q2	Japan	2000	FY21Q2Japan	0.1	200
FY21Q3	Japan	3000	FY21Q3Japan	0.1	300
FY21Q4	Japan	4000	FY21Q4Japan	0.1	400

Geo Table

Geo

India

Japan

Date Table

Fisacl Quarter

FY21Q1

FY21Q2

FY21Q3

FY21Q4

Input Table

Merge	QTR	Capital Lease	Geo
FY21Q1India	FY21Q1	50%	India
FY21Q2India	FY21Q2	50%	India
FY21Q3India	FY21Q3	50%	India
FY21Q4India	FY21Q4	50%	India
FY21Q1Japan	FY21Q1	10%	Japan
FY21Q2Japan	FY21Q2	10%	Japan
FY21Q3Japan	FY21Q3	10%	Japan
FY21Q4Japan	FY21Q4	10%	Japan

Order Value = CALCULATE([Amount New]*sum(Inputs[Capital Lease])) 

I want to calculate order value such that when a Geo filter is selected its respective capital lease is taken into account

I get this results perfectly.

However, when multiple values or all values in a filter are selected then order value should be calculated at geo level first and then summed up for all Geos , instead of summing of capital lease and then calculating order value.

How can i do this?

PBIX Link

https://drive.google.com/file/d/1WXsFssi-BS-WiZNVSTchzS5RQqOcoafa/view?usp=sharing

[Anonymous · Accepted solution]

Hi @klehar ,

I suggest you to create calculated column instead.

Please check the below formulas.

C_Capital Lease = LOOKUPVALUE(Inputs[Capital Lease],Inputs[Geo],Orders[Geo])

C_Order Value = Orders[Amount]*Orders[C_Capital Lease]

Result:

Best Regards,

Jay

[klehar]

@Greg_Deckler @ and @lbendlin 
tagging since i coundt get a reply

[klehar]

@parry2k tagging for expertize