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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculate Per Unit Number when Sold Totals are within Data Field

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.

 

Per Unit Result.JPGP&L Data.JPG 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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:

 

2021 Sold = calculate(sum('P&L Data'[2021]), allexcept('P&L Data','P&L Data'[Natural]),'P&L Data'[Natural] = "911000" || 'P&L Data'[Natural] = "912000" || 'P&L Data'[Natural] = "913000")

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

Maybe this would help: https://youtu.be/IGCIsCC5iXQ?

Anonymous
Not applicable

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.

Anonymous
Not applicable

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:

 

2021 Sold = calculate(sum('P&L Data'[2021]), allexcept('P&L Data','P&L Data'[Natural]),'P&L Data'[Natural] = "911000" || 'P&L Data'[Natural] = "912000" || 'P&L Data'[Natural] = "913000")
Anonymous
Not applicable

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...

 

Test Formula.JPG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors