How to create measure for ROI=REV/Total ?

[admin11]

Hi All

[v-deddai1-msft · Accepted solution]

Hi @admin11 ,

Try to use the following measure for NP%:

NP % =
DIVIDE (
    [SUM_YTD REV&COGS&EXP&OTH],
    CALCULATE (
        [SUM_YTD REV&COGS&EXP&OTH],
        FILTER ( _PNL_Table, _PNL_Table[REV&COGS] = "REV" )
    )
)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[admin11]

Hi All

https://www.dropbox.com/s/zdulxh0rapjrsx8/PBT%20GL%20VER0067%20how%20to%20compute%20ROI.pbix?dl=0

My PBI file

Paul

[amitchandak]

@admin11 , Please find if the attached file can help

[admin11]

@amitchandak 

Thank you for sharing. 

My Problem Statement :

How to compute ROI = REV/Net Profit=0.59 is correct using Amit expression below :-

RoI 1 = DIVIDE(CALCULATE([SUM_YTD REV&COGS&EXP&OTH] , FILTER(_PNL_Table,_PNL_Table[REV&COGS] = "REV")),[SUM_YTD REV&COGS&EXP&OTH])

But now i like to compute NP % = Net Profit/REV , I using below expression :-

RoI 3_ = DIVIDE(CALCULATE(

FILTER(_PNL_Table,_PNL_Table[REV&COGS] = "REV")

,

[SUM_YTD REV&COGS&EXP&OTH]

),

FILTER(_PNL_Table,_PNL_Table[REV&COGS] = "REV")

)

I get error msg :-

A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

May i know how to clear the error msg ?

My PBI file :-

https://www.dropbox.com/s/l7dqafwlowkj45g/GL_ISDN_v009%20ROI%20solution%20ask%20AGAIN.pbix?dl=0

Paul