Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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
Solved! Go to 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
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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
73 | |
71 | |
55 | |
38 | |
31 |
User | Count |
---|---|
71 | |
64 | |
60 | |
50 | |
45 |