- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
A row separation for 2 by a condition in another column. Power Query
Hey everyone!
Fighting with next task and have no idea, how to solve it. Really need a help
We've got a list of invoices. And a planned sum of amount per month.
Invoices are sorted by customer+year+period and Ascending sum of amount per invoice.
1. So the target is to find last invoice on which running total of Fact Amount becomes higher than Planned Amount.
2. Separate this invoice for two (rest of amount to achieve the sum of Total Planned Amount; the rest sum of this invoice)
3. Give the statuses for any of these rows depending on it's sense
4. All other invoices over the plan is resting with their values, but are changing their status for Over Plan
Please, see the image below
Any help will be highly appreciated! Thanks a lot!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This is likely too complex for Power BI.
Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. I cannot use screenshots of your source data.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hey @lbendlin
Thank you for your try to help.
Here is the link for Excel file with Existing and Target tables.
Also there is a formula, based on which I can make these calculation in Excel and it shows the whole logic
Thank you
https://www.dropbox.com/scl/fi/hdtrsq16p3s4jk7vf3bde/Sample-Power-BI.xlsx?dl=0&rlkey=t7pzrjzorevf802...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
The first part (cumulative column N) can probably be done in Power BI. But the splitting of the overshot invoice into two parts and the subsequent logic change is likely too much. I would recommend you continue to use Excel.
Also note that your key column sort is alphabetic, not by date.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@lbendlin
Sorry for disappered for a week, got private stuff.
Thank you for reviewed the case anyway.
Will try to make it with macro
Thanks!
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - January 2025
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
Subject | Author | Posted | |
---|---|---|---|
08-08-2024 07:49 AM | |||
11-27-2023 07:37 AM | |||
02-09-2024 09:31 AM | |||
06-10-2024 04:28 AM | |||
05-22-2024 11:36 AM |
User | Count |
---|---|
100 | |
69 | |
42 | |
37 | |
30 |
User | Count |
---|---|
157 | |
89 | |
62 | |
46 | |
40 |