cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hcze
Helper I
Helper I

Financial statement

Dear experts,

 

I am trying to build a financial report to show in below format using matrix: 

Note that the revenue has no children

hcze_0-1594365092029.png

 

So far, below is the format that I managed to get:

hcze_1-1594365187324.png

 

and this is the data structure that I am currently using

hcze_2-1594365401027.png

 

Any help is appreciated. Please note that I need expand/collapse to work as well (the actual statement is huge with much more financial items)

 

Thanks,

hcze

1 ACCEPTED SOLUTION

Hi @hcze ,

it seems you're looking for a ragged hierarchy where drilldown/level expansion stops once there are no more children:

 

image.png

 

This isn't natively supported in Power BI and you need some data modelling and DAX sorcery to achieve this.

I've blogged about this method here: https://www.thebiccountant.com/2019/10/03/parent-child-hierarchies-with-multiple-parents-in-power-bi...

But realized that a dedicated blogpost for your kind of task would be helpful as well, as the data has to be prepared in a special way. Hopfully I find the time to write this up soon.

However, I'm enclosing a sample file that hopefully helps you to apply this to your data.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

6 REPLIES 6
yingyinr
Community Support
Community Support

Hi @hcze ,

Please check if the below screen shot is what you want. If yes, you can refer my sample pbix file to get it.

Financial statement.JPG

Best Regards

Rena

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @hcze ,

it seems you're looking for a ragged hierarchy where drilldown/level expansion stops once there are no more children:

 

image.png

 

This isn't natively supported in Power BI and you need some data modelling and DAX sorcery to achieve this.

I've blogged about this method here: https://www.thebiccountant.com/2019/10/03/parent-child-hierarchies-with-multiple-parents-in-power-bi...

But realized that a dedicated blogpost for your kind of task would be helpful as well, as the data has to be prepared in a special way. Hopfully I find the time to write this up soon.

However, I'm enclosing a sample file that hopefully helps you to apply this to your data.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Awesome! That's exactly what I am looking for. Thanks!

amitchandak
Super User
Super User

@hcze , refer if this can help

https://community.powerbi.com/t5/Desktop/Traditional-Financial-Statements/td-p/7223

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
Greg_Deckler
Super User
Super User

@hcze - Take a look at this: https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/...

 

I realize that your goal is slightly different. However, I think you should be able to achieve your goals by using the disconnected table trick to create a custom hierarchy as in the link above. It's just you would use that hierarchy on your rows instead of your columns.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Thanks Greg.

 

I used that trick before but unfortunately I still need to be able to collapse/expand and that trick doesnt address that.

I am sure I am not the only one having this issue 🙂

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.