Forum Discussion

NSC7's avatar
NSC7
Helper I
2 years ago
Solved

How to do selective row total in Matrix

Hi All,

I need help with matrix total. below is the table screenshot.

ASP= Rev * Unit

$= (ASP Current month - ASP Prev month) * Unit of current month

 

Total of $ should only total for current year. Dec-22 also has $ value but I dont want to display the $ value for Previous year also Total should consider only current years total.

 

 

Thanks

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi NSC7 ,

    Please try to create  measure with below dax formula:

    $ =
VAR _a =
    SELECTEDVALUE ( 'Table'[Month-Year] )
VAR _b =
    SELECTEDVALUE ( 'Table'[Product] )
VAR _c =
    CALCULATE (
        [ASP],
        FILTER (
            ALL ( 'Table' ),
            [Product] = _b
                && DATEDIFF ( [Month-Year], _a, MONTH ) = 1
        )
    )
VAR _d =
    SELECTEDVALUE ( 'Table'[Unit] )
VAR _e = [ASP]
VAR cur_year =
    YEAR ( TODAY () )
VAR _result =
    IF ( YEAR ( _a ) = cur_year, ( _e - _c ) * _d, BLANK () )
RETURN
    _result

    ASP = SELECTEDVALUE('Table'[Rev])*SELECTEDVALUE('Table'[Unit]) 
    Measure = IF(ISINSCOPE('Table'[Month-Year]),[$],SUMX('Table',[$]))

    Add a matrix visual with fields and measure:

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    NSC7 , Try like

    Assume you have date table and month year is coming from that

     

    =

    var _val = (ASP Current month - ASP Prev month) * [Unit of current month]

    return

    if(Year(Max(date[Date])) < Year(Today()) , blank(), _val )

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi NSC7 ,

    Please try to create  measure with below dax formula:

    $ =
VAR _a =
    SELECTEDVALUE ( 'Table'[Month-Year] )
VAR _b =
    SELECTEDVALUE ( 'Table'[Product] )
VAR _c =
    CALCULATE (
        [ASP],
        FILTER (
            ALL ( 'Table' ),
            [Product] = _b
                && DATEDIFF ( [Month-Year], _a, MONTH ) = 1
        )
    )
VAR _d =
    SELECTEDVALUE ( 'Table'[Unit] )
VAR _e = [ASP]
VAR cur_year =
    YEAR ( TODAY () )
VAR _result =
    IF ( YEAR ( _a ) = cur_year, ( _e - _c ) * _d, BLANK () )
RETURN
    _result

    ASP = SELECTEDVALUE('Table'[Rev])*SELECTEDVALUE('Table'[Unit]) 
    Measure = IF(ISINSCOPE('Table'[Month-Year]),[$],SUMX('Table',[$]))

    Add a matrix visual with fields and measure:

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.