cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
Community Support

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors