Revenue divided by Expense

[Anonymous]

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.

One table contains the GL No. and the SubCategory that those GL No. belong too. The second table contains the general ledger entries like dates, amounts, GL No.

I need to work out 3 separate calulations

1. Operating Expenses divided by Revenue
2. Other Expenses  divided by Revenue
3. Wages & Salaries divided by Revenue

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.

Thank you

The relationship is GLNo.

[sturlaws · Accepted solution]

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.

[harshnathani · Accepted solution]

Hi @Anonymous ,

You need to create measures and not Calculated Column

Attached is file.

https://drive.google.com/file/d/1L3ZF3FpUB3poIcInAraxhyVuIzlEsrEZ/view?usp=sharing

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[sturlaws · Accepted solution]

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]

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]

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]

Ha ! Good one.

I will test that code out and report back.

Thank you for helping