Forum Discussion

Ritaf's avatar
Ritaf
Icon for Responsive Resident rankResponsive Resident
3 years ago
Solved

Aggregation for category which doesn't appear at visual

Hi there, I am trying to calculate the sum by category as I won't use this category in my visual, so I can't do it with : Test= CALCULATE( sum('Table'[score]),ALLEXCEPT(table,'table'[abc_item_ID]))...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • mangaus1111's avatar
    mangaus1111
    3 years ago

    Hi Ritaf1983 ,

    try this measure

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

mangaus1111's avatar
mangaus1111
Icon for Solution Sage rankSolution Sage
3 years ago

Hi , could you please send us a sample table and your expected result?

  • Ritaf's avatar
    Ritaf
    Icon for Responsive Resident rankResponsive Resident
    3 years ago

    Hello,
    I have attached a picture of the data and the expected result.
    For every OrdersCustomerItemId, I need to calculate the sum of POunitsQuantity without using OrdersCustomerItemId in the visual.
    Right now I am using a calculated column with an earlier function, and I am wondering if there is a better way to accomplish this.
    Thank you, Rita

     

  • Ritaf1983's avatar
    Ritaf1983
    Icon for Super User rankSuper User
    3 years ago

    Thanks for your assistance.
    Are you sure that your solution is better than the calculated column I created: 

    QTYbyCorder = sumx(filter('factOrdersPurchase',factOrdersPurchase[OrdersCustomerItemId]=EARLIER(factOrdersPurchase[OrdersCustomerItemId])),'factOrdersPurchase'[QTY])
    Since this measure is one of many in this report, I don't need another table. However, I know that earlier is a very wasteful function, so I wondered if there was a way to calculate it with a measure using a virtual table.
    • mangaus1111's avatar
      mangaus1111
      Icon for Solution Sage rankSolution Sage
      3 years ago

      Hi Ritaf1983 ,

      try this measure

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • Ritaf's avatar
        Ritaf
        Icon for Responsive Resident rankResponsive Resident
        3 years ago

        That's great! The solution I was looking for! I appreciate your help 🙂