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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
netanel
Post Prodigy
Post Prodigy

P&L Help +- & Sort

Hi community,
Two questions I'm having trouble with
I have a financial statement the Description in the table come from an Dim account
The amount comes from the FACT table
Everything is connected well 
the problem
1. I would like a certain account to bring me a plus amount and another account to bring me a minus amount
For example Revenues will be positive
And FEE will be negative
I have a hard time doing this

 

2. I would like to determine the order of the sections in the visual
Regolar solutions don't work for me because each section comes from a different place in the account hierarchy

I would appreciate your help!

Thanks

 

Link for exmple my case

https://1drv.ms/f/s!AonyYI-TdspHgUhwngZ6OKvpnSsc

 

 

 

 

 

 








Did I answer your question?
Mark my post as a solution!
Appreciate your Kudos!

Connect on Linkedin
linkedin.com/in/netanel-shriki
8 REPLIES 8
lbendlin
Super User
Super User

1.  Add a calculated column that inverts the sign for the fees but keeps it for the revenues

2. Create a reference table with the sections and their desired sort order as an index.  Then use "Sort one column by another column"

 

The fact table seems to be missing the account reference?

Hi @lbendlin 

 

I am trying to create a correct plus and minus sign in my numbers
I have an agg(+,-) column where the signs are correct
And I have an Amount column.
Because when I multiply between them something falls

The logic of the measure says Amount * Agg (column sign)
The rest of the formula is ignoring other filters

 

2022-08-04_11-19-47.png








Did I answer your question?
Mark my post as a solution!
Appreciate your Kudos!

Connect on Linkedin
linkedin.com/in/netanel-shriki

Please update your sample file with the account reference.

 

updated

+ column Agg(+,-) in Dim Account

Thanks








Did I answer your question?
Mark my post as a solution!
Appreciate your Kudos!

Connect on Linkedin
linkedin.com/in/netanel-shriki

fact table is still empty

 

lbendlin_0-1659641341185.png

 

Sori,
updated








Did I answer your question?
Mark my post as a solution!
Appreciate your Kudos!

Connect on Linkedin
linkedin.com/in/netanel-shriki

Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

 

Unfortunately your sample data is still not in a usable shape.

 

You also seem to expect to be able to use data from different levels of the hierarchy.  While that is possible it will make the measure very complex as you will have to account for all hierarchy level scenarios separately to avoid double counting. You may want to rethink that process.

Hi @lbendlin 

Sorry for the delay and the mess
I really didn't specify the data a bit because I thought I would get a general direction
But thanks for the answer and the desire to help
I have attached in the link both a PBIX file and a detailed Excel file that contains my example
Unfortunately, the data is sensitive and it is difficult for me to prepare an example case
I will send you another message regarding this matter
Beyond that, I think that the sample data will be able to give a clearer direction this time

 

https://1drv.ms/f/s!AonyYI-TdspHgUhwngZ6OKvpnSsc

expected outcome:

What you see in the Visual sheet in Excel


Thanks!








Did I answer your question?
Mark my post as a solution!
Appreciate your Kudos!

Connect on Linkedin
linkedin.com/in/netanel-shriki

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.