How to change my PnL table OTH label to GP% ?

[admin11]

Hi All,

Below expression will create 4 row of value label :-

_PnL_Table_N =

VAR t_rev =

FILTER ( DISTINCT ( GL[1_REV] ), NOT ( ISBLANK ( GL[1_REV] ) ) )

VAR t_cogs =

FILTER ( DISTINCT ( GL[1_COGS] ), NOT ( ISBLANK ( GL[1_COGS] ) ) )

VAR t_oth =

FILTER ( DISTINCT ( GL[1_OTH] ), NOT ( ISBLANK ( GL[1_OTH] ) ) )

VAR t_exp =

FILTER ( DISTINCT ( GL[1_EXP] ), NOT ( ISBLANK ( GL[1_EXP] ) ) )

VAR t_rev_cogs =

UNION ( t_rev, t_cogs, t_oth, t_exp )

VAR tbl = {(t_rev, 1), (t_cogs, 2), (t_oth, 3) , (t_exp, 4)}

RETURN

tbl

May i know how to change OTH to GP% ?

Paul

[v-yiruan-msft · Accepted solution]

Hi  @admin11 ,

The formula VAR tbl = {(t_rev, 1), (t_cogs, 2), (t_oth, 3) , (t_exp, 4)} is used to construct a table as below:

You can update the formula of your calculated table as below, the part with red font is updated one:

_PnL_Table_N =
VAR t_rev =
    FILTER ( DISTINCT ( GL[1_REV] ), NOT ( ISBLANK ( GL[1_REV] ) ) )
VAR t_cogs =
    FILTER ( DISTINCT ( GL[1_COGS] ), NOT ( ISBLANK ( GL[1_COGS] ) ) )
VAR t_oth =
    FILTER ( DISTINCT ( GL[1_OTH] ), NOT ( ISBLANK ( GL[1_OTH] ) ) )
VAR t_exp =
    FILTER ( DISTINCT ( GL[1_EXP] ), NOT ( ISBLANK ( GL[1_EXP] ) ) )
VAR t_rev_cogs =
    UNION ( t_rev, t_cogs, t_oth, t_exp )
VAR tbl = { ( t_rev, 1 ), ( t_cogs, 2 ), ( "GP%", 3 ), ( t_exp, 4 ) }
RETURN
    tbl

In addition, you can refer the following links to create a static table using table constructor:

Creating a Table in Power BI Using DAX Table Constructor

Best Regards

[admin11]

Hi All

VAR tbl = {(t_rev, 1), (t_cogs, 2), (t_oth, 3) , (t_exp, 4)}

May I know the purpose of above code ? 

it was given by some one , some time ago , now I need to modify .