cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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

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

1 ACCEPTED SOLUTION
Frequent Visitor

Solution

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 =
CALCULATETABLE (
SUMMARIZE (fact_table, fact_table[Date], fact_Table[prod_id], fact_Table[value], dim_ProductTable[Max])
,fact_table[Date] = MAX ( fact_table[Date] )
)
)
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)

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

Frequent Visitor

Solution

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 =
CALCULATETABLE (
SUMMARIZE (fact_table, fact_table[Date], fact_Table[prod_id], fact_Table[value], dim_ProductTable[Max])
,fact_table[Date] = MAX ( fact_table[Date] )
)
)
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

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors