Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I am building a Power BI P&L using raw data from our accounting system. The data contains a list of accounts/entities with the appropriate values for each month. I would like to calculate per unit values for revenues, expenses, etc. However, the sold totals are in specific accounts within the data. I have tried to create custom fields that would filter out the sold data using if statements and calculate/filters. However, this only populates the sold date within the sold accounts. The attachedpictures are examples of how the data is set up, along with the results I'm getting trying to filter out the sold volume.
Solved! Go to Solution.
I was able to build on this response and figure out what I needed. I used the below formula to pick out the sold volume for each year:
Unfortunately, that is not what I'm looking for. I have the P&L built in the format we want, with revenues, expenses, etc. I need to find a way to pull out the Volume from the data, and then divide the revenues/expenses by that total volume number. Any thoughts on how to do that?
Hi @Anonymous ,
If you want to calculate the volumes of ID for each Account number and Account Description fields, you can create a measure like this:
Count =
CALCULATE (
COUNT ( 'Table'[Period ID] ),
ALLEXCEPT ( 'Table', 'table'[Account Number], 'table'[Account Description] )
)
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I was able to build on this response and figure out what I needed. I used the below formula to pick out the sold volume for each year:
Thank you for the response. This seems close to what I need, just not quite there. As you can see from this image, I am hoping to return the 522,313 total sold number, and the formula is bringing back 700,583. Is there a way to adjust the calculate/allexcept formula to only sum the totals from specific accounts? I would then need to divide all other numbers by this result...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.