cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills 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

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors