Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
16 | |
13 | |
12 | |
11 | |
11 |
User | Count |
---|---|
19 | |
14 | |
14 | |
11 | |
9 |