How can I sum a formula where other columns are the minimum

[RafaelPinto]

Hi, this is my first time asking here, I'm doing this report for my job so any help is well appreciated. 

I created a metric with Calculate that sums a formula if the value of a column (numkar) is the maximum for each product (numers 02190,02191,....).

To-CosCom = CALCULATE(
SUMX(fa_kardex,
IF(LEFT(fa_kardex[tipkar],1)="C",
fa_kardex[stkalm]*fa_kardex[stkfra]+fa_kardex[stkalm_m]+fa_kardex[qtypro]*fa_kardex[stkfra]+fa_kardex[qtypro_m],
fa_kardex[stkalm]*fa_kardex[stkfra]+fa_kardex[stkalm_m]-fa_kardex[qtypro]*fa_kardex[stkfra]-fa_kardex[qtypro_m])
*fa_kardex[coscom]/fa_kardex[stkfra]),
fa_kardex[numkar]= MAX(fa_kardex[numkar]))

It's working well for the products but when I want to sum these values for a category of products (in this case Laboratory, like ABBOT NUTRITIONAL) it gives me the minimum value of all the products from the category, not the sum, which is understandable because of the CALCULATE filter. 

I want to know how can I make that it sums the values and only apply the filter to the products and not to the categories, so I can sum the group.

These are my tables used where many products (fa_productos[codpro]) belong to one laboratory (fa_laboratorios[Laboratorio]) and fa_kardex is the transaction table with many records for each product (thats why I used a filter in the CALCULATE that gives me the maximum transaction code = the latest).

[Anonymous]

Hi  @RafaelPinto ,

I created some data:

Here are the steps you can follow：

1. Create measure.

Flag =
IF(
    ISINSCOPE('Table'[group])&&NOT(ISINSCOPE('Table'[group1])),SUMX('Table',[To-CosCom]),[To-CosCom])

2. Result:

Best Regards,

Liu Yang

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

[MFelix]

Hi @RafaelPinto ,

Try to change you calculation to:

To-CosCom =
CALCULATE (
    SUMX (
        fa_kardex,
        IF (
            LEFT ( fa_kardex[tipkar], 1 ) = "C",
            fa_kardex[stkalm] * fa_kardex[stkfra] + fa_kardex[stkalm_m] + fa_kardex[qtypro] * fa_kardex[stkfra] + fa_kardex[qtypro_m],
            fa_kardex[stkalm] * fa_kardex[stkfra] + fa_kardex[stkalm_m] - fa_kardex[qtypro] * fa_kardex[stkfra] - fa_kardex[qtypro_m]
        ) * fa_kardex[coscom] / fa_kardex[stkfra]
    ),
    fa_kardex[numkar] in VALUES( fa_kardex[numkar] )
)

I have replaced the MAX by a VALUES syntax this means that at a level of the product it will consider one product but at the level of the category will consider all products that are within that categorie