Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
CH/OL = CALCULATE(SUM('Detail'[Amount Financed]),'detail'[doc type]<>"Operating Lease",'detail'[Doc type]<>"Contract Hire")
 
Im using this but its not actually picking up the data correctly 
What is wrong with the calculation
Anonymous's avatar
Anonymous
Not applicable
4 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
    4 years ago

    This is still not picking up the data correctly 

     

    Is there any other explanation for why it may not work?

     

    The calculation method for the rest of the process is correct but its the first calculation which is the issue which means the results for the rest of the process are an issue 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 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
    4 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
    4 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
    4 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"}))