ADDING ONE WITH CONDITIONS

[Syndicate_Admin]

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 · Accepted solution]

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

[v-jiewu-msft · Accepted solution]

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]

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]

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.

[danextian · Accepted solution]

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