Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
klehar
Helper V
Helper V

Conditional calculation based on filter value selected

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 QuarterGeoAmountMergeLeaseOrder Value
FY21Q1India100FY21Q1India0.550
FY21Q2India200FY21Q2India0.5100
FY21Q3India300FY21Q3India0.5150
FY21Q4India400FY21Q4India0.5200
FY21Q1Japan1000FY21Q1Japan0.1100
FY21Q2Japan2000FY21Q2Japan0.1200
FY21Q3Japan3000FY21Q3Japan0.1300
FY21Q4Japan4000FY21Q4Japan0.1400

Geo Table

Geo
India
Japan

 

Date Table

Fisacl Quarter
FY21Q1
FY21Q2
FY21Q3
FY21Q4

 

Input Table

MergeQTRCapital LeaseGeo
FY21Q1IndiaFY21Q150%India
FY21Q2IndiaFY21Q250%India
FY21Q3IndiaFY21Q350%India
FY21Q4IndiaFY21Q450%India
FY21Q1JapanFY21Q110%Japan
FY21Q2JapanFY21Q210%Japan
FY21Q3JapanFY21Q310%Japan
FY21Q4JapanFY21Q410%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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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:

1.jpg

 

Best Regards,

Jay

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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:

1.jpg

 

Best Regards,

Jay

klehar
Helper V
Helper V

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

klehar
Helper V
Helper V

@parry2k tagging for expertize

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.