Why my expression not return NET_PROFIT ?

[Paulyeo11]

Hi All

My expression below working fine no error :-

PNL =

SWITCH(TRUE(),

'Table'[GL CODE]>=20000 &&'Table'[GL CODE]<30000,"COGS",

'Table'[GL CODE]>=40000 &&'Table'[GL CODE]<50000,"REV",

'Table'[GL CODE]>=80000 && 'Table'[GL CODE]<100000,"EXP",

'Table'[GL CODE]>=10000 && 'Table'[GL CODE]<100000,"NET_PROFIT",

BLANK())

The issue is it does not display NET_PROFIT .

Hope some one can advise me why ?

Paul

[v-eqin-msft · Accepted solution]

Hi @Paulyeo11 ,

Because all these conditions: [20000,30000), [40000,50000), [80000,100000)  are in the range of [10000,100000).

The picture below may help you understand.

If the condition"NET_PROFIT"  is written in the first, the following conditions will "cover" it.

And If it  is written in the last, we could use IF( ) statement to understand:  [10000,100000) is not the negative condition of the above condition, so the last logical sentence could not run. 

Column =
IF (
    'Table'[GL CODE] >= 20000
        && 'Table'[GL CODE] < 30000,
    "COGS",
    IF (
        'Table'[GL CODE] >= 40000
            && 'Table'[GL CODE] < 50000,
        "REV",
        IF (
            'Table'[GL CODE] >= 80000
                && 'Table'[GL CODE] < 100000,
            "EXP",
            IF (
                'Table'[GL CODE] >= 10000
                    && 'Table'[GL CODE] < 100000,
                "NET_PROFIT",
                BLANK ()
            )
        )
    )
)

Maybe you could create one more column for "NET_PROFIT" or apply conditional formatting for the PNL column.

Measure =
IF (
    MAX ( 'Table'[GL CODE] ) >= 10000
        && MAX ( 'Table'[GL CODE] ) < 100000,
    "Yellow"
)

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,
Eyelyn Qin

[Paulyeo11]

Hi Eyelyn

Thank you for sharing and provide me detail explianation . 

i try both of the expression is share. 

I still cannot figure how how to compute the net profit amount . 

it is possible you share the PBI file with me ?

Paul Yeo

[manikumar34]

@Paulyeo11 ,

if you see the table NET PROFIT is covering all other 3 metric values where your GL CODE is between 20000 and 89999, and you are trying to didvide the values between as below which is your DAX and also for net profit again you are referring the GL CODE from 10000 to 100000 whcih is already covered on the other Metrics. 

Because of whcih either you can get Net Profit as th total column or the other 3 defined values. 

Table'[GL CODE]>=80000 && 'Table'[GL CODE]<100000,"EXP",

'Table'[GL CODE]>=40000 &&'Table'[GL CODE]<50000,"REV",

'Table'[GL CODE]>=20000 &&'Table'[GL CODE]<30000,"COGS",

Regards, 

Manikumar

[Paulyeo11]

Hi Sir

I think my raw data example is not correct.

I should create a correct raw data , so that i can develop a P&L statement.

As it have been confirm by so many expert here.

Paul Yeo

[manikumar34]

@Paulyeo11 , 

According your claculation Net Pfofit is on the highest level and calculating on all the values of the column. So your Net Profit will  become entire column vlaue else you can have other values apart from Net Profit.

In this case have two calculated columsn one with Net Profit and the other calculated column with other metrics. 

Regards, 

Manikumar

[manikumar34]

@Paulyeo11 

I see you have already passed those values which are between 10000 to 100000 and again you are trying to pass the same on Net Profit. This makes the Value Contradiction. Because of which if you take Net Profi first you can see all the columns with Net Profit and if you takle at last you don'y see net profit. 

[amitchandak]

@Paulyeo11 , if it 10000 then it should be at last if 100,000 then it should be first

PNL =
SWITCH(TRUE(),
'Table'[GL CODE]>=100000 && 'Table'[GL CODE]<100000,"NET_PROFIT",
'Table'[GL CODE]>=80000 && 'Table'[GL CODE]<100000,"EXP",
'Table'[GL CODE]>=40000 &&'Table'[GL CODE]<50000,"REV",
'Table'[GL CODE]>=20000 &&'Table'[GL CODE]<30000,"COGS",

BLANK())

or

PNL =
SWITCH(TRUE(),

'Table'[GL CODE]>=80000 && 'Table'[GL CODE]<100000,"EXP",
'Table'[GL CODE]>=40000 &&'Table'[GL CODE]<50000,"REV",
'Table'[GL CODE]>=20000 &&'Table'[GL CODE]<30000,"COGS",
'Table'[GL CODE]>=10000 && 'Table'[GL CODE]<100000,"NET_PROFIT",

BLANK())

[Paulyeo11]

Hi Amit

https://www.dropbox.com/s/udjf31pw0zsjegd/C0021%20GL_%20TDS%20v0003.pbix?dl=0

I have try both your expression , NET PROFIT never appear

Paul