Calculated column with sum of all sales for each id

[caiowirthmann]

Hi everyone!

I'm fairly new to Power Bi and DAX and I need help creating a column that adds all the sales values for each "Code_ID" on a table that contains all the registered "Code"

For context I added a .xlsx with sample data that is used on my report (simplified with less rows only the relevant data, and also for privacy) to help solving my problem. Both tabs in the excel sheet are different tables in PowerBi that comes from differentLink to sample data:
Sample data (weTransfer link)

I need to create a column on "CODE BASE" table that adds all the "Sales_R$" value for each "Code_Id" on the "Sales by Code" table (excel formula example provided on "C" column in "CODE BASE" tab) Each "Code_Id" is distinct for each "Store Number", but "Code_Id" can repeat each month/year. So far there's no need to add these values by month or year since these will be filtered on visual by a calendar table used as data segmentation.

The reason I need this column is to facilitite my ABC classification (that is done using "Code_Id" total sales as a base). The resulting ABC classes are used as a data segmentation on my visuals only, so all the calculations could be done on the table itsell

I tried creating this column using FILTER, EARLIER, SUMX, CALCULATE but I couldnt get it to work, probably due to my inexperience

I hope that I provided enough context and explained what I'm trying to do

[v-chenwuz-msft · Accepted solution]

Hi @caiowirthmann ,

You can try this code to create column:

sum of all sales for each id =
CALCULATE(
    SUM( 'Sales By Code'[Sales_R$] ),
    FILTER( 'Sales By Code', [Code_Id] = EARLIER( 'CODE BASE'[Code_Id] ) )
)

result

Best Regards

Community Support Team _ chenwu zhu

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

[caiowirthmann]

It could've been a bug or something else, but after I deleted and redid all relationships on my model and used the following code I got the results I expected

SomaTodasAsVendasPorID =
CALCULATE (
    SUMX ( 'Base_Vendas_Transacao_Nubimetrics', [Vendas_R$] ),
    FILTER (
        'Base_Vendas_Transacao_Nubimetrics',
        [CODIGO_ANUNCIO] = EARLIER ( [CODIGO_ANUNCIO] )
    )
)

Thanks for the help @v-chenwuz-msft and @Samarth_18 

[caiowirthmann]

I still get the same "error" as the solution presented by samarth_18. I get a blank column as a result, no values or #error

[caiowirthmann]

Hi @Samarth_18 

I tried your code (changing names to the actual ones on my tables), but I get no result on it. No errors on the code itself, the created column is blank. Both values for "Code_ID" on both tables are stored as text, Sales_R$ as decimal value and there's a MANY TO MANY relationship for CODE_Id with a "both" filter

I attached pictures of the code and resulting column, and also of the relationship between the tables

[Samarth_18]

@caiowirthmann , Can you try to add trim in CODIGO_ANUNCIO column 

Column = 
CALCULATE (
    SUM ( 'Base_Vendas_Transacao_Nubimetrics'[Sales_R$] ),
    FILTER (
        ALL ( 'Base_Vendas_Transacao_Nubimetrics' ),
        TRIM('Base_Vendas_Transacao_Nubimetrics'[CODIGO_ANUNCIO ]) = TRIM('Base_Anuncios'[CODIGO_ANUNCIO ])
    )
)

Note:- Since its a many to many relationship then you will see same sum for all the matching records.

[caiowirthmann]

Even by adding TRIM I still get a blank column with no values or error

[Samarth_18]

Hi @caiowirthmann ,

Create a column with below code:-

Column = 
CALCULATE (
    SUM ( 'Sales By Code'[Sales_R$] ),
    FILTER (
        ALL ( 'Sales By Code' ),
        'Sales By Code'[CODE_Id] = 'CODE BASE'[Code_Id]
    )
)

Output:-

Thanks,

Samarth