P&L statement, Total Sales not working

[Anonymous]

Hi all,
please help me with the following,
I'm doing a P&L table, and can't figure out how to return a Total Sales.

first let me give you the following info:
1. My P&L template (Table name = GCOA) has the following structure, where ID column is "Code", and P&L uses values from Template column

2. It has connections with Cost and Sales table, (naturally these tables don't have Total Sales Code, only Cost and Sales codes)

I started with simple calculations:

1. Services_R40000 = CALCULATE([m_act_sales], GCOA[Code] = "R40000")
2. RevenueRebilled_R40210 = CALCULATE([m_act_sales], GCOA[Code] = "R40210"),
all seems to be calculating right, but when I try calculating Total Sales, it returns me blank,

even though, on it's own , measure works, 
Total Sales = CALCULATE([Services_R40000], ALL(GCOA[Code])) + CALCULATE([RevenueRebilled_R40210], ALL(GCOA[Code])))

I also tried the following:

P&L_values =

SWITCH(

    SELECTEDVALUE(GCOA[Code]),

    "R40000", [m_act_services_R40000],

    "R40210", [m_act_other_R40210],

    "PL0001", CALCULATE([Services_R40000], ALL(GCOA[Code])) + CALCULATE([RevenueRebilled_R40210], ALL(GCOA[Code])))

I figure out that the issue is that I use codes, but P&L shows only difinitions, and when I try with codes, it works,
but still returns blank for Total Sales

is there a way to assign formula that PL0001 = R40000 + R40210 and also PL0001 = Total Sales???

I tried to do this, so to make Power BI calculate sum to PL0001 and return it as Total Sales:

Total_Sales_PL0001 =

SWITCH(

    SELECTEDVALUE(GCOA[Code]),

    var _codes = SELECTEDVALUE(GCOA[Code])

    return

        SWITCH(

            TRUE(),

            SUM(GCOA[Template Sorting]) = 1100, SELECTEDVALUE(GCOA[Code])),

    CALCULATE([Services_R40000], ALL(GCOA[Code])) + CALCULATE([RevenueRebilled_R40210], ALL(GCOA[Code])))

but it didn't help,

I checked all previous posts, found this one,
https://community.fabric.microsoft.com/t5/Desktop/P-amp-L-Help/m-p/2112115#M782536

but it didn't help as well

[Martin_D]

Hi Ali_Shakh,

There are many tutorials on the internet for financial reproting with Power BI with slightly different approaches. Since for Power BI financial reporting is not a straight forward use case, it's worth to dig into a tutorial to build your solution on a solid foundation. My personal favorite approach is the one from Matt Allington https://exceleratorbi.com.au/build-a-pl-with-power-bi/

Your current problem is that your subtotal lines are just additional lines with text indentation which do not relate to the any rows in your values table. Instead, you need a hierarchy that you can use to activate matrix visual's subtotal experience.

Solution

Instead of loading indented lines for your layout, load totaling rows as additional columns and stack them as rows into a matrix visual.

Not

Code	Template
PL0003	Total Net Sales
PL0001	Total Sales
R40000	Services
R40025	Customer Rebates

You need

Code	Row	Subtotal	Total
R40000	Services	Total Sales	Total Net Sales
R40025	Customer Rebates	Total Sales	Total Net Sales

Do you see how Total Sales now relates to all the codes of the corresponding values?

Although this approach will solve your problem, I recommend to work through the tutorial and then you can decide which approch is most benefitial for your scenario.

[Anonymous]

hi @Martin_D ,
 

thanks for your response 🙂

tbh, using a simple hierarchical table was my initial idea,

but then i watched this video on How to Power BI and changed it
https://youtu.be/J4317R5BvsA  

it still bugs me, that I can't solve it the way i described,
anyways thank's for the link

[Martin_D]

The major difference between your data model and the one in the video is that Bas has no relationship between the report template table and the values table. If you want to build the solution from the video you need to fully adopt the data model structure and measure. Then Bas gives you the option to build a table using text indentation or a matrix using total rows.

You

Relation

Bas

No relatioship