Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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.
Solved! Go to Solution.
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.
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)
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)
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
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")))))))))))))))
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
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |