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
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.