Sum of product which went over max value

[Twe-Maker]

Hey, im trying to sum the product that went over max value to category on a matrix. Still dont understand DAX

dim_CategoryTable

id	name
c1	cat1
c2	cat2

dim_ProductTable

prod_id	name	max	cat_id
p1	prod1	5	c1
p2	prod2	5	c2

fact_Table

id	prod_id	value	date
4	p1	6	2.4.22
3	p2	4	2.4.22
2	p1	5	1.4.22
1	p2	4	1.4.22

i hope the result like this on matrix

cat name          prod id	max	Alert
-cat1		1
prod1	5	6 !!
-cat2		0
prod2	5	4

CountAlert = 
VAR _prod =
SUMX(
VALUES(fact_table[prod_id])
,VAR _User = fact_table[prod_id]
VAR _MaxDate =
CALCULATE( MAX(fact_table[Date]),fact_table[prod_id] = _User)
VAR Amount = 
CALCULATE(SUM(fact_table[value]), fact_table[prod_id] = _User , fact_table[Date] = _MaxDate)
RETURN
Amount
)
VAR _cat =
??????????????????
VAR _Total =
SUMX(
VALUES(fact_table[prod_id])
,VAR _User = fact_table[prod_id]
VAR _MaxDate =
CALCULATE(MAX(fact_table[Date]),fact_table[prod_id] = _User) 
VAR Stutse = 
CALCULATE(SUM(fact_table[value]),dim_ProductTable[type] = "User_All", fact_table[prod_id] = _User,fact_table[Date]= _MaxDate)
RETURN
Stutse
)
RETURN
IF(
ISINSCOPE(dim_CategoryTable[name])
,IF(ISINSCOPE(dim_productTable[name]), _prod,_cat )
,_Total
)

Thanks

[v-rongtiep-msft]

Hi @Twe-Maker ,

I hvae created a simple sample, please refer to my pbix file.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

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

[Twe-Maker]

Hey, thank you for the response. It didnt really worked. I continue using ur pbix file and change some stuff(data model and added one measure)

CountAlert = 
VAR _prod =
SUMX(
VALUES(fact_table[prod_id])
,VAR _User = fact_table[prod_id]
VAR _MaxDate =
CALCULATE( MAX(fact_table[Date]),fact_table[prod_id] = _User)
VAR Amount = 
CALCULATE(SUM(fact_table[value]), fact_table[prod_id] = _User , fact_table[Date] = _MaxDate)
RETURN
Amount
)
VAR _cat =
COUNTX(
VALUES(fact_table[prod_id])
,VAR _User = fact_table[prod_id]
VAR _MaxDate =
CALCULATE(MAX(fact_table[Date]),fact_table[prod_id] = _User) 
VAR Stutse =
CALCULATE(SUM(fact_table[value]), fact_table[prod_id] = _User, fact_table[Date]= _MaxDate)
RETURN
Stutse
)
VAR _Total =
SUMX(
VALUES(fact_table[prod_id])
,VAR _User = fact_table[prod_id]
VAR _MaxDate =
CALCULATE(MAX(fact_table[Date]),fact_table[prod_id] = _User) 
VAR Stutse = 
CALCULATE(SUM(fact_table[value])/*,dim_ProductTable[type] = "User_All"*/, fact_table[prod_id] = _User,fact_table[Date]= _MaxDate)
RETURN
Stutse
)
RETURN
IF(
ISINSCOPE(dim_CategoryTable[name])
,IF(ISINSCOPE(dim_productTable[name]), _prod,_cat )
,_Total
)

row cat2 should be 0 because lastest value from prod2 didnt go over max. My measure for cat is wrong, now it just count the amount of prod_id under cat

[Twe-Maker · Accepted solution]

Solution

CountAlert = 
VAR _prod =
    SUMX(
        VALUES(fact_table[prod_id])
            ,VAR _User = fact_table[prod_id]
            VAR _MaxDate =
                CALCULATE( MAX(fact_table[Date]),fact_table[prod_id] = _User)
            VAR Amount = 
                CALCULATE(SUM(fact_table[value]), fact_table[prod_id] = _User , fact_table[Date] = _MaxDate)
            RETURN
            Amount
    )
VAR _cat =
    VAR TOTALM =
        ADDCOLUMNS (
            CALCULATETABLE (
                SUMMARIZE (fact_table, fact_table[Date], fact_Table[prod_id], fact_Table[value], dim_ProductTable[Max])
                ,fact_table[Date] = MAX ( fact_table[Date] )
            )
            ,"Alert", IF (dim_ProductTable[Max] < fact_Table[value],1)
        )
                RETURN
                        SUMX(TOTALM, IF (ISBLANK([Alert]), 0 , 1) )
    // COUNTX(
    //     VALUES(fact_table[prod_id])
    //         ,VAR _User = fact_table[prod_id]
    //         VAR _MaxDate =
    //             CALCULATE(MAX(fact_table[Date]),fact_table[prod_id] = _User) 
    //         VAR Stutse =
    //             CALCULATE(SUM(fact_table[value]), fact_table[prod_id] = _User, fact_table[Date]= _MaxDate)
    //         RETURN
    //         Stutse
    // )
VAR _Total =
    SUMX(
        VALUES(fact_table[prod_id])
            ,VAR _User = fact_table[prod_id]
            VAR _MaxDate =
                CALCULATE(MAX(fact_table[Date]),fact_table[prod_id] = _User) 
            VAR Stutse = 
                CALCULATE(SUM(fact_table[value])/*,dim_ProductTable[type] = "User_All"*/, fact_table[prod_id] = _User,fact_table[Date]= _MaxDate)
            RETURN
            Stutse
    )
RETURN
    IF(
        ISINSCOPE(dim_CategoryTable[name])
        ,IF(ISINSCOPE(dim_productTable[name]), _prod,_cat )
        ,_Total
    )