Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
makdaddy
Helper II
Helper II

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.

 

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.

2 ACCEPTED SOLUTIONS
sturlaws
Resident Rockstar
Resident Rockstar

Hi, @makdaddy 

 

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.

View solution in original post

harshnathani
Community Champion
Community Champion

Hi @makdaddy ,

 

 

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)

View solution in original post

5 REPLIES 5
harshnathani
Community Champion
Community Champion

Hi @makdaddy ,

 

 

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
Resident Rockstar
Resident Rockstar

Hi, @makdaddy 

 

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.

Hello Sturlaws

 

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

 

See attached screen shot 

2020-06-25 09_57_39-Clipboard.png

 

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"

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" )
    )
)

Ha ! Good one.

 

I will test that code out and report back.

 

Thank you for helping

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors