Forum Discussion
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 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
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
3 Replies
Greg_Deckler @ and lbendlin
tagging since i coundt get a replyHi 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
