Forum Discussion

nenadpekec's avatar
nenadpekec
Frequent Visitor
5 years ago
Solved

Matrix, custom calculation in Total Column

Hello, I have test data like this: NOTE: you can download pbix file here. Scenario: Users are being interviewed and it is possible that the same user gets interviewed multiple times d...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, nenadpekec 

    Please check the below picture and the sample pbix file's link down below.

     

     

    Values Latest Measure =
    VAR currentq =
    MAX ( 'Table'[Questions] )
    VAR currentuser =
    MAX ( 'Table'[User] )
    VAR latestdate =
    CALCULATE (
    MAX ( 'Table'[Date] ),
    FILTER (
    ALLSELECTED ( 'Table' ),
    'Table'[Questions] = currentq
    && 'Table'[User] = currentuser
    )
    )
    RETURN
    CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
    ALLSELECTED ( 'Table' ),
    'Table'[Questions] = currentq
    && 'Table'[User] = currentuser
    && 'Table'[Date] = latestdate
    )
    )
     
     
    Values Latest Measure Total Fix =
    IF (
    ISFILTERED ( 'Table'[Date] ),
    SUM ( 'Table'[Value] ),
    SUMX ( VALUES ( 'Table'[User] ), [Values Latest Measure] )
    )
     
     
     
     

    Hi, My name is Jihwan Kim.


    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM

Jihwan_Kim's avatar
Jihwan_Kim
Super User
5 years ago

Hi, nenadpekec 

Please check the below picture and the sample pbix file's link down below.

 

 

Values Latest Measure =
VAR currentq =
MAX ( 'Table'[Questions] )
VAR currentuser =
MAX ( 'Table'[User] )
VAR latestdate =
CALCULATE (
MAX ( 'Table'[Date] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Questions] = currentq
&& 'Table'[User] = currentuser
)
)
RETURN
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Questions] = currentq
&& 'Table'[User] = currentuser
&& 'Table'[Date] = latestdate
)
)
 
 
Values Latest Measure Total Fix =
IF (
ISFILTERED ( 'Table'[Date] ),
SUM ( 'Table'[Value] ),
SUMX ( VALUES ( 'Table'[User] ), [Values Latest Measure] )
)
 
 
 
 

Hi, My name is Jihwan Kim.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

nenadpekec's avatar
nenadpekec
Frequent Visitor
5 years ago

Thank you, very much!