Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Anonymous ,

 

Try 

CH/OL = CALCULATE(SUM('Detail'[Amount Financed]),FILTER('Detail','Detail'[doc type]<>"Operating Lease" && 'Detail'[Doc type]<>"Contract Hire"))

 

If you want to remove other filters, add the ALL/ALLSELECTED function

CH/OL = CALCULATE(SUM('Detail'[Amount Financed]),FILTER(ALL('Detail'),'Detail'[doc type]<>"Operating Lease" && 'Detail'[Doc type]<>"Contract Hire"))

or

CH/OL = CALCULATE(SUM('Detail'[Amount Financed]),FILTER(ALLSELECTED('Detail'),'Detail'[doc type]<>"Operating Lease" && 'Detail'[Doc type]<>"Contract Hire"))

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Step 1: calculations are diff tried two but give same results but this is the issue as data is not being picked up correctly 
CH/OL = CALCULATE(SUM('Detail'[Amount Financed]),FILTER('Detail','Detail'[doc type]<>"Operating Lease" && 'Detail'[Doc type]<>"Contract Hire"))
1 HP/FL = CALCULATE(SUM('Detail'[Amount Financed]),'detail'[doc type]<>"Hire Purchase",'detail'[Doc type]<>"Finance Lease")
 
step 2:
CH/OL plus 1 HP/FL =
[CH/OL] + [1 HP/FL]
 
Step 3:
CH/OL% = Divide ('Key Measures'[CH/OL],'Key Measures'[CH/OL plus 1 HP/FL],0)
HP/FL% = Divide ('Key Measures'[1 HP/FL], 'Key Measures'[CH/OL plus 1 HP/FL],0)
 
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Just a thought but could it be <> in the formula as I need it to add up amount financed values for all of the CH and OL 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    almost solved the issue

     

    So I am using the fact table to get the figures but the details for the managers are coming from a connecting table as i need to use two table for a differernt calculation 

    therefore, if i add the manager details from the same table it works but if i use the connecting table it does not work.

    but that means the other calculation i need will not work so either way there is an issue 

    What can i di to make both work or use the connecting table 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago
    Also changed formula to this =

    HP/FL 2 = CALCULATE(SUM(Detail[Amount Financed]), FILTER(Detail,Detail[Doc Type] IN {"Hire Purchase", "Finance Lease"}))
    CH/OL 3 = CALCULATE(SUM(Detail[Amount Financed]), FILTER(Detail,Detail[Doc Type] IN {"Contract Hire", "Operating Lease"}))