Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Profit and Loss problems

Hello,
I have a problem with my profit and loss calculation. For these I have an account hierarchy with a handful of special cases. In theory, it can be booked to all accounts. However, due to the hierarchical structure, some accounts are also under accounts from the upper ones. Furthermore, individual accounts can have several different parents, and the weighting in the parents can be with different signs.
Example (1): Account A11 (level 3) is to be included in A1 (level 2) negatively and in B1 (level 2) positively.
Example (2): Account A113 (level 4) is to be posted positively. However, the value in A13 (level 3) is to be displayed as negative.

grafik.png

 

I was able to solve problem 1 with SUMX by summing up the account bookings using the row context. However, an incorrect value is displayed for the postings from A13, since A113 is positively and only gets the negative sign with A13. In the rowcontext, however, the corresponding booking is not displayed (since it has a positive weighting for the line from A113 is).grafik.png

 

In this example, a different +/- sign should have been used since the weighting of A13 is -1.

 

I have enclosed all the bookings.

grafik.png

 

i am grateful for any help

 

Bruening

1 REPLY 1
Anonymous
Not applicable

Here's one article about hierarchy handling: https://www.sqlbi.com/articles/clever-hierarchy-handling-in-dax/

and here's another one that uses the new ISINSCOPE function to simplify things:

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

Best
D

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors