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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
admin11
Memorable Member
Memorable Member

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

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

admin11_0-1645931094336.png

May i know how to change OTH to GP% ?

 

Paul

1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

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:

yingyinr_2-1646207452261.png

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

yingyinr_0-1646204278275.png

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

yingyinr_1-1646204561646.png

Best Regards

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

View solution in original post

2 REPLIES 2
v-yiruan-msft
Community Support
Community Support

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:

yingyinr_2-1646207452261.png

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

yingyinr_0-1646204278275.png

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

yingyinr_1-1646204561646.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
admin11
Memorable Member
Memorable Member

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 . 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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