Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more
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).
@RafaelPinto , Try like
Sumx(Values(fa_kardex [ABBOT NUTRITIONAL]) ,
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])) )
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.
Check out the November 2025 Power BI update to learn about new features.
Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!
| User | Count |
|---|---|
| 21 | |
| 10 | |
| 9 | |
| 4 | |
| 4 |
| User | Count |
|---|---|
| 34 | |
| 31 | |
| 19 | |
| 13 | |
| 10 |