cancel
Showing results for
Did you mean:
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

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors