Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Revenue divided by Expense

This link contails a zip file with the example I am trying to produce.   I would like to determine the position of a company by calculating the required categories divided by the total revenue.   ...
  • sturlaws's avatar
    sturlaws
    6 years ago

    Hi, Anonymous 

     

    I am a bit confused about this sentence:
    The challenge I have is that the GL Entries that contain the Revenue Lines which need to be calcuated as a total of all the lines and not just exclude them so that we get a total of the operating Expesnses divided by the total revenue.

     

    Could you check if this gives you what you are after:

    Operating expenses / revenue =
DIVIDE (
    CALCULATE (
        SUM ( GeneralLedger[Amount] ),
        FILTER ( 'COASort', COASort[Classification] = "Operating Expenses" )
    ),
    CALCULATE (
        SUM ( GeneralLedger[Amount] ),
        FILTER ( ALL ( 'COASort' ), COASort[Classification] = "Revenue" )
    )
)

     

    Cheers,
    Sturla

    If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

sturlaws's avatar
sturlaws
Icon for Resident Rockstar rankResident Rockstar
6 years ago

Hi, Anonymous 

 

I am a bit confused about this sentence:
The challenge I have is that the GL Entries that contain the Revenue Lines which need to be calcuated as a total of all the lines and not just exclude them so that we get a total of the operating Expesnses divided by the total revenue.

 

Could you check if this gives you what you are after:

Operating expenses / revenue =
DIVIDE (
    CALCULATE (
        SUM ( GeneralLedger[Amount] ),
        FILTER ( 'COASort', COASort[Classification] = "Operating Expenses" )
    ),
    CALCULATE (
        SUM ( GeneralLedger[Amount] ),
        FILTER ( ALL ( 'COASort' ), COASort[Classification] = "Revenue" )
    )
)

 

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hello Sturlaws

     

    Sorry for the confusion i think you are on the right track here though.

     

    See attached screen shot 

     

    here is the code i attempted to reuse

    Operating expenses / revenue = 
DIVIDE ( 
    CALCULATE 
        ( SUM ( G_LEntries[MTD Actual] ), FILTER ( 'COASort', COASort[Top Level Classification] = "Operating Expenses" ) ,
    CALCULATE 
        ( SUM ( G_LEntries[MTD Actual] ),  FILTER ( ALL ( 'COASort', COASort[Top Level Classification]] = "Revenue")))))))))))))))
     
    The error message is "The end of the input was reached"
    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      6 years ago

      yeah, that code has the sniffles.

       

      Count the opening and closing brackets - they need to eventually zero out, both for the round ones and for the square ones.

       

      Operating expenses / revenue =
DIVIDE (
    CALCULATE (
        SUM ( G_LEntries[MTD Actual] ),
        FILTER ( 'COASort', COASort[Top Level Classification] = "Operating Expenses" )
    ),
    CALCULATE (
        SUM ( G_LEntries[MTD Actual] ),
        FILTER ( ALL ( 'COASort' ), COASort[Top Level Classification] = "Revenue" )
    )
)
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Ha ! Good one.

         

        I will test that code out and report back.

         

        Thank you for helping