Income Statement Custom Subtotal and hierarchy

[Sdhn420]

Hi all,

I am struggling to get my income statement right. Here is the sample pbix file. https://drive.google.com/file/d/1ccoFO5LUpYd8zyouDX_3SxdKogKdTWiO/view?usp=sharing

I have a Data table, master and calendar tables. 

Have created measures and used switch() to display calculated subtotals but it is not appearing. Also, the heirarchy is not right. 

It should be - 

Sales

Cost of Sales

Net Sales (measure created) = Sales - Cost of Sales

Could you pls review the pbix file and advise.

@amitchandak @mahoneypat  @Fowmy 

[aj1973]

Hi,

I think The best way to go through the whole process is to start organizing and grouping your GL accounts by their category and then create your measures. 

In your Model, if you need to use Net Sales Value as it is you will need to create a column in you Data Table

DESC 1 = RELATED(Master[PL Description 1])

Then use it in your 'Net Sales Value' = 

CALCULATE(SUM('Data'[Value]),Data[DESC 1]="SALES") - CALCULATE(SUM('Data'[Value]),Data[DESC 1]="GROSS SALES")

Your P&L Value measure is not correct as you can't use Switch for the purpose. what do you want to achieve? that's why I suggest you better start grouping your GL accounts.

[Sdhn420]

Thanks @aj1973 

I have created a column as you suggested and a measure for net sales value. 

How can I make it appear on matrix row without using switch?

[aj1973]

@Sdhn420 

Well there is 2 ways to achieve your goal.

The first is to follow the video that @MattAllington attached(good video), the video is pretty much close to what you have as tables and columns in your Model.

My way however is different: I don't add columns nor Tables to the model. I only use the GL_transactions Table and a Calendar Table, I groupe the GL Accounts by their categories(Number then description) and i create measures for each group and Category. The reason : Measures don't take as much space in the Model as new Tables and Columns, they have better performance, easy to organize inside the Matrix(no need for Sort column), better performance in Power BI service and when it is Exported back from the service to Excel, at most the user will only need to transpose the columns rather than using Power pivot tables feature(in case some users don't know how to use Power Pivot). Also with measures you can build KPIs for Notifications in case there was a newly GL account added to the model, or to check the Balance if it is different than $0. 

Sure my way is little longer way but better readable espacially when you name your measures correctly and accordingly.

Choose your way Pal,

[Anonymous]

Hi @aj1973 even I have a G/L table entries only and i have made measures for each and every Header as @MattAllington mentioned in his video.

now challenge is I'm not able to make subtotal header.
Secondly i have a budget coming in excel format which i want to involve in the martix table and even for that i have made measures and when i put that total revenue measures in the matrix than it adds up in row level rather than column level.

hope you get it.

[aj1973]

Hi @Anonymous 

Not quite sure understood your request. However here is a video that could help "Maybe"

https://www.youtube.com/watch?v=neycI2arPbw
Let me know

[Anonymous]

hi @aj1973 

please check this request posted that I have posted ,if this makes you understand my problem.

How to indulge Budget in matrix visual in Income s... - Microsoft Power BI Community

[aj1973]

@Anonymous 

Just replied to your thread.

[MattAllington]

My video here may help you https://exceleratorbi.com.au/build-a-pl-with-power-bi/

[Sdhn420]

@MattAllington  When i try to sort, the value of the measure changes. Pls advise.