Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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.

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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")

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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?

      • v-yingjl's avatar
        v-yingjl
        Community Support
        5 years ago

        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.