It there any VIZ for display P&L summary table

[admin11]

Hi Card

I like to create a summary P&L Table :-

Rev ......... 100K

COGS........80K

PROFIT.....20K

EXP............10k

NP..............10k

I have try the 3 different type of card all not suitable :-

Paul

[v-cazheng-msft]

Hi, @admin11 

In your formula, the name of Measures used don't match the Measures ‘_YTD_COGS’, ‘_YTD EXP’ and ‘_YTD COGS’ in your file. And there are some columns with yellow alert information. You need cope with them firstly, then you can try this Measure:

Table =

UNION (

    SUMMARIZE ( 'Date', "REV", [_LYTD REV] ),

    SUMMARIZE ( 'Date', "COGS", [_YTD COGS] ),

    SUMMARIZE ( 'Date', "Exp", [_YTD EXP] )

)

For more details about SUMMARIZE function, you can refer Power BI SUMMARIZE and All secrets of SUMMARIZE.

Best Regards,

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[admin11]

@v-cazheng-msft 

Thank you for sharing , i have try :-

https://www.dropbox.com/s/ghegrnsmqbjg70e/PBT%20GL%20VER0025%20how%20to%20create%20PNL%20summerise%2...

above is my PBI file :-

Paul

[aj1973]

@admin11 

Add ROUNDDOWN to the formula

[v-cazheng-msft]

I'm confused with the data '376,760' and '583,679'. Can you explain how to get them?

Best Regards 

Caiyun Zheng

[admin11]

@v-cazheng-msft 

The issue already solve. now working fine. that are the old post.

Thank you very much 

Paul

[v-cazheng-msft · Accepted solution]

Hi, @admin11 

You can try the following steps.

1 Create a Calculated table

Table =

VAR t_cogs =

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

VAR t_exp =

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

VAR t_cogs_exp =

    UNION ( t_cogs, t_exp )

RETURN

    SELECTCOLUMNS ( t_cogs_exp, "COGS&EXP", [1_COGS] )

2 Create a Measure

_YTD COGS&EXP = IF(SELECTEDVALUE('Table'[COGS&EXP])="COGS",[_YTD COGS],[_YTD EXP]/1000)

3 Crate a Table visual, the result looks like this:

Best Regards,

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[admin11]

@v-cazheng-msft 

i Try to add one more field _YTD_OTH

First i create below Table_ it work fine , no error  :-

Table_ =

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_rev_cogs_oth =

UNION ( t_rev, t_cogs, t_oth )

RETURN

SELECTCOLUMNS ( t_rev_cogs_oth, "REV&COGS&OTH", [1_REV] )

I dont get any error , until i try to create the measure :-

_YTD REV&COGS&OTH = IF(SELECTEDVALUE('Table_'[REV&COGS&OTH])="COGS",[_YTD COGS],[_YTD REV],[_YTD OTH])

Error code :-

Too many arguments were passed to the IF function. The maximum argument count for the function is 3.

my PBI file :-

https://www.dropbox.com/s/52ejc1n6x2y9uq3/PBT_V2021_170%20Create%20REV%20and%20COGS%20EXP%20P%26L%20...

Paul

[v-cazheng-msft]

You can try this Measure.

_YTD COGS&EXP&OTH =
SWITCH (
TRUE (),
'Table'[COGS&EXP&OTH] = "COGS", [_YTD COGS],
'Table'[COGS&EXP&OTH] = "EXP", [_YTD EXP],
'Table'[COGS&EXP&OTH] = "OTH", [_YTD OTH]
)

Best Regards,

Caiyun Zheng

[admin11]

@v-cazheng-msft 

I have try , i get error :-

My PBI file :-

https://www.dropbox.com/s/jfaoy214rn9v2zb/PBT_V2021_170%20Create%20REV%20and%20COGS%20EXP%20P%26L%20...

Paul

[v-cazheng-msft]

It is caused by the table name error. In the formula, the table name is 'Table', but the table name in your pbix file is 'Table_'. Just change 'Table' to 'Table_'.

Best Regards

Caiyun Zheng

[admin11]

@v-cazheng-msft

i start a new track to solve the issue , hope you can participate as well :- 

https://community.powerbi.com/t5/Desktop/Problem-Statement-Create-P-amp-L-Summary-Table-for-GL-ANALY...

Paul

[admin11]

@v-cazheng-msft 

I have just try to change the table to table_ , still cannot.

By the way :-

Table for test with 2 field REV and COSG

Table_ for test with 3 field REV and COGS and OTH 

Can you refer the link above  , and try to help me make it work and share with me the working PBI file.

Thank 

Paul 

[admin11]

@v-cazheng-msft 

Thank you so much for your help , it work fine now.

Paul

[aj1973]

@admin11 

Act here

[admin11]

Hi AJ

Thank you for your help , i try but still not get waht i want :-

Paul

[amitchandak]

@admin11 , Not very clear, a new table like

union(
summarize('Table',"Var","Rev" ,[Rev] ),
summarize('Table',"Var","COGS" ,[COGS] ),
)

or a matrix visual with no row or column , with values and "Show on row"

https://www.burningsuit.co.uk/blog/2019/04/7-secrets-of-the-matrix-visual/

[admin11]

@amitchandak 

Thank you very much.  I try to union 3 table into 1 for below field name :-

I get error msg :-

https://www.dropbox.com/s/6r9z25lna28krzg/PBT%20GL%20VER0025%20how%20to%20create%20PNL%20summerise%2...

My PBI file :-

Paul