Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
RafaelPinto
Regular Visitor

How can I sum the minimum values made with calculate?

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. 

RafaelPinto_1-1640214117460.png

 

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_0-1640267228558.png

 

2 REPLIES 2
amitchandak
Super User
Super User

@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])) )

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.