Need help in p&l report where columnwise values should be shown in row wise

[sajjadkhan25]

Hi Team,@amitchandak,@valtteriN

Greetings for the day,

I have all cogs,revenue values in column and with respect to that I need to calculate GM.

But the view of report should be in row wise,

• Which can be easily done by unpivot but client is not ready to accept that so I need your help to how to get data from columns and put in row wise and even calculate GM dynamically. please a request help me on this. Thank you hope i get response soon.

[amitchandak]

@sajjadkhan25 , 1. Show on row in matrix

There is a video from Curbal, very recently, how to deal with financial statement, check that out

this take advantage of few features or excel and power bi

https://www.youtube.com/watch?v=IISYzTaIyu4

[sajjadkhan25]

Hi @amitchandak sir,

actaully the solution what i want is, in the form of 

NATURES ACTUAL/BUDGET
Revenue 70
COGS 50
Standard Allocation 10
GM 10
GM% 0.33%

but the way i have data is in form of,

Date COGS REVENUE STD ALLOC
1/8/2020
Client is not okay with unpivot has it will increase number of rows so please help me how shall i give the desired output.

Thanks,
Sajjad

[sajjadkhan25]

Hi @amitchandak sir,

actaully the solution what i want is, in the form of 

NATURES ACTUAL or BUDGET
Revenue 70
COGS 50
Standard Allocation 10
GM 10
GM% 0.33%

but the way i have data is in form of,

Date COGS REVENUE STD ALLOC
01-08-2020 50 70 10

Client is not okay with unpivot has it will increase number of rows so please help me how shall i give the desired output.

Thanks,
Sajjad

[v-chenwuz-msft · Accepted solution]

Hi @sajjadkhan25 ,

Is this above you want ?

You can use unpivot in power bi query. Select date column then unpivot other column.

And the result as below.

Then in matrix table, add [NATURES] to rows will get the result.

Or show on rows, in the format of matrix table, search show on rows as following:

If i misunderstood you, please share some sample data and expect result.

Best Regards

Community Support Team _ chenwu zhu

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