Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
2 years ago
Solved

ADDING ONE WITH CONDITIONS

Good morning

I want to add the "UNITS" column according to product, color and size per agent.

For example, Luis, 3 units, but it doubles me in this case to 6 units by the supplier.

Another example, Pedro, 30 units.

Thank you!

  • danextian's avatar
    danextian
    2 years ago

    Hi Syndicate_Admin 

    If you want to calculate the sum of units by agent and supplier, try this:

    CALCULATE (
    SUM ( 'table'[UNIDADES] ),
    ALLEXCEPT ( 'table', 'table'[AGENTE], 'table'[PROVEEDOR] )
)
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Syndicate_Admin ,

    Based on my testing, please try the following methods:

    1.Create the sample table.

    2.Create the calculated table.

    Unique = SUMMARIZE(
       'Table', 
       'Table'[AGENT], 
       'Table'[PRODUCT], 
       'Table'[COLOR], 
       'Table'[SIZE], 
       "UniqueUnits", MAX('Table'[UNIDADES])
   )

    3.Create the relationship between two tables.

    4.Create the new Units column. 

    Units = 
CALCULATE(
           SUM('Unique'[UniqueUnits]), 
           ALLEXCEPT('Unique', 'Unique'[AGENT])
       )

    5.The result is shown below.

    Best Regards,

    Wisdom Wu

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

4 Replies

  • danextian's avatar
    danextian
    Super User
    2 years ago

    Hi Syndicate_Admin 

    If you want to calculate the sum of units by agent and supplier, try this:

    CALCULATE (
    SUM ( 'table'[UNIDADES] ),
    ALLEXCEPT ( 'table', 'table'[AGENTE], 'table'[PROVEEDOR] )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Syndicate_Admin ,

    Based on my testing, please try the following methods:

    1.Create the sample table.

    2.Create the calculated table.

    Unique = SUMMARIZE(
       'Table', 
       'Table'[AGENT], 
       'Table'[PRODUCT], 
       'Table'[COLOR], 
       'Table'[SIZE], 
       "UniqueUnits", MAX('Table'[UNIDADES])
   )

    3.Create the relationship between two tables.

    4.Create the new Units column. 

    Units = 
CALCULATE(
           SUM('Unique'[UniqueUnits]), 
           ALLEXCEPT('Unique', 'Unique'[AGENT])
       )

    5.The result is shown below.

    Best Regards,

    Wisdom Wu

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

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Administrator
      2 years ago

      Hello

      First thank you for your message.

      The results I want to get are the total units that I consult with the suppliers.

      that is, in the 240801 consultation, agent Luis requests prices of 2 large red notebooks and 1 small green pencil. In total, Luis asks for a quote for 3 units from two different suppliers.

      When adding the units in PowerBi it adds as many units as suppliers I have consulted. In this case it adds up to the 6 units because I have consulted two suppliers, but in reality I only consult 3 units (2 notebooks + 1 pencil).

      Thank you

      • danextian's avatar
        danextian
        Super User
        2 years ago

        Hi Syndicate_Admin ,

         

        If you want to the units to counted just once by Agent, Product, Color and Size,  use SUMX and SUMMARIZE in a measure.

        Sum of Units = 
SUMX (
    SUMMARIZE (
        'Table',
        'Table'[ID],
        'Table'[AGENT],
        'Table'[PRODUCT],
        'Table'[COLOR],
        'Table'[SIZE],
        'Table'[UNIDADES]
    ),
    [UNIDADES]
)

        Please see the attached pbix.