Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Twe-Maker
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

idname
c1cat1
c2

cat2


dim_ProductTable

prod_idnamemaxcat_id
p1prod15c1
p2prod2

5

c2

fact_Table

idprod_idvaluedate
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
maxAlert
-cat1 1
       prod156 !!
-cat2 0
      prod254

 

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 _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

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
    )

 

TweMaker_0-1673307243347.png

 

 

 

View solution in original post

3 REPLIES 3
v-rongtiep-msft
Community Support
Community Support

Hi @Twe-Maker ,

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

vpollymsft_0-1672797612151.png

How to Get Your Question Answered Quickly 

 

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.

 

 

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)

TweMaker_0-1672824906161.png

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 =
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

TweMaker_1-1672824990302.png

 

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
    )

 

TweMaker_0-1673307243347.png

 

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors