cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Sum of product which went over max value

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)RETURNAmount)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)RETURNStutse)RETURNIF(ISINSCOPE(dim_CategoryTable[name]),IF(ISINSCOPE(dim_productTable[name]), _prod,_cat ),_Total)`

Thanks

1 ACCEPTED SOLUTION
Frequent Visitor

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

3 REPLIES 3
Anonymous
Not applicable

Hi @Twe-Maker ,

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

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.

Frequent Visitor

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)RETURNAmount)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)RETURNStutse)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)RETURNStutse)RETURNIF(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

Frequent Visitor

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.