topic How can I sum the minimum values made with calculate? in DAX Commands and Tips

How can I sum the minimum values made with calculate?

RafaelPinto — Thu, 23 Dec 2021 13:50:50 GMT

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

Re: How can I sum the minimum values made with calculate?

amitchandak — Thu, 23 Dec 2021 03:20:04 GMT

RafaelPinto , Try like

Sumx(Values(fa_kardex [ABBOT NUTRITIONAL]) ,

Re: How can I sum the minimum values made with calculate?

RafaelPinto — Thu, 23 Dec 2021 13:43:30 GMT

I tried
SUMX(VALUES(fa_laboratorios[Laboratorio]),

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 didn't work, still gives me 0 and not the sum. I didn't specified but in my example Abbot Nutritional is part of the categories of Laboratories (many products belong to one laboratory) and it's related by a table of Products (that connect the product code with the laboratory code) and a table of Laboratories (that connect the laboratory code with the laboratory name). The fact table where I want to apply the individual formula is fa_kardex where there are many registers for each product, thats why I use a filter of the maximum code (numkar) wich is the latest for each product. I want to sum these values by categories but when I do it gives me latest of all the category and not the sum of all the values that belong to that category.