P&L Report not display all row information

[admin11]

Hi All

@VijayP share with me one PBI P&L report it working fine , to create this P&L report it require only 5 measure :-

Below link have raw 1 GL raw data file and 2 set up file & 1 PBI file all working fine in below Drop box link :-

https://www.dropbox.com/sh/62pxp29mqdwxcnl/AADXXyDOQeD_M29ObkZoE2k-a?dl=0

Sonce it is only having 5 measure , So I try to create a PBI file by following step :- 

Step 1 :- Copy the above dropbox 3 file to C:/Temp folder

Step 2 :- Create a new PBI file name = PAUL.PBIX

Step 3 :- I import GL raw data and 2 Acc setup file file to PBI using CSV load from C:/Temp.

Step 4 :- I open GL_WORKING.PBIX file and copy all 6 measure and paste to PAUL.PBIX.

Date = CALENDAR(Date(2000,01,01),date(2021,12,31))

Anuual Total_1 =

VAR Summary = SELECTEDVALUE(Format_PnL[Sub Category])

VAR Category =SELECTEDVALUE(Format_PnL[Category])

RETURN

SWITCH(TRUE(),

SELECTEDVALUE(Format_PnL[Sub Category])="Total Sales",[Total Sales_1],

SELECTEDVALUE(Format_PnL[Sub Category])="Total cost of Sales",[Total Cost of Sales_@],

SELECTEDVALUE(Format_PnL[Sub Category])="GROSS PROIFT/(LOSS)",[Gross Profit_],

CALCULATE([Total Profit_],FILTER(GL_TI,GL_TI[GL Description]=Category)))

Total Sales_1 = CALCULATE(SUM(GL_TI[C/D]),Fin_Category[Summary]="Sales")*-1

Total Profit_ =

VAR Revenue = CALCULATE(SUM(GL_TI[C/D]),Fin_Category[Type]="Revenue")*-1

VAR Expenses = CALCULATE(SUM(GL_TI[C/D]),Fin_Category[Type]="Expenses")*-1

Return

Revenue+Expenses

Total Cost of Sales_@ = CALCULATE(SUM(GL_TI[C/D]),Fin_Category[Summary]="COST OF SALES")*-1

Gross Profit_ = [Total Sales_1]+[Total Cost of Sales_@]

Step 5 :- I don't get any error , But i cannot duplicate the same P&L report as above. May i know where go wrong ?

Paul Yeo

[parry2k]

@admin11 a piece of advice, you need to know your data and your data model, it is basics. You would have saved a lot of your time if you know that the Type column is the right column or the column you used doesn't contain the correct information. Anyhow, glad it all worked out.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[admin11]

@parry2k 

Yes , doing data analysis is very exciting. especially seeing @VijayP how he manage to turn and twist and data more exciting then sex.

Paul

[parry2k · Accepted solution]

I sent you GL_NOTWORKING.PBIX exactly the same name as you sent to me. I don't think you are mixing too many things at your end. It is the same file, same name, the same everything, just measure is fixed.

[admin11]

@parry2k 

You are right , i mix up too many thing.

Finally i found out the issue , it is due to the wrong Format_PnL i give the wrong data value. after i change , it work fine now.

In this lession , i get to learn :-

- it is not enough to just check and make sure all table field are same , make sure the value inside the table must be same , especially for PNL report. 

[parry2k]

@admin11 I didn't change anything in the model, I just fixed that measure. Not sure what issues you are running into, it is your file, your data, and just fixed the measure, not sure how I can help from here.

[parry2k]

@admin11 solution is attached. tweak it as you see fit.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[admin11]

@parry2k 

I think you have send the wrong file , the file you send to me is @VijayP PBI file , because when i check your PBI file , the SOURCE i load from share point. where my PBI file is loading from C:/TEMP fold. Hope you can send again.

Paul

[admin11]

@parry2k I am getting very confuse. when i try to reload the PBI file you send me , the table getting error. Can you pls send me accounting setup file = Fin_Category , i think it is cause by this raw data file is different from mine.

Paul

[parry2k]

@admin11 I already provided you the fix, is it not working for you? Not sure what is your question?

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@admin11 your this measure is incorrect, it is using the Category column whereas it is supposed to be Type. Type column is the one that contains "revenue" or "expenses" value.

Total Profit_2 = 
VAR Revenue = CALCULATE(SUM(GL_TI[C/D]),Fin_Category[Type]="Revenue")*-1
VAR Expenses = CALCULATE(SUM(GL_TI[C/D]),Fin_Category[Type]="Expenses")*-1
Return
Revenue+Expenses

output:

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[admin11]

@parry2k 

Wow you are real good , can understand the data model very well. I have try to modify the Fin_Category "TYPE" field value label , from REV to Revenue and EXP to Expenses.

But still unable to display Annual Total Amount.

Can you share with me the PBI file which you already make it work here ?

Paul

[parry2k]

@admin11 do both files have everything same? Same measure etc? I don't want to spend time comparing the files, so if you confirm then I can take a look.

[admin11]

@parry2k 

Thank you for sharing with me how to check , I have just make both Pbi file having  same field name . using below link to get both PBI file :-

https://www.dropbox.com/sh/nzawrgzhu5nmvnp/AAB3Wh_7es6-e71FafJkpuIwa?dl=0

Paul

[parry2k]

@admin11 compare the relationship between tables on working and non-working pbix.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[admin11]

@parry2k

Thank you very much for sharing the great hint. 

I have just try to link those field , but still not working , below my PBI file :-

https://www.dropbox.com/s/b45y510f2h6z48l/GL_NOTWORKING.pbix?dl=0

Paul