Forum Discussion

Sdhn420's avatar
Sdhn420
Icon for Helper IV rankHelper IV
5 years ago

Income Statement Custom Subtotal and hierarchy

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, ma...
Need Help
aj1973's avatar
aj1973
Icon for Community Champion rankCommunity Champion
5 years ago

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's avatar
Sdhn420
Icon for Helper IV rankHelper IV
5 years ago

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's avatar
    aj1973
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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.