cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
BIAB Regular Visitor
Regular Visitor

Calculating row value by Column value

I want to be able to multiply row values from a table by the column values, in this case the column value is the  month number/period of each column. 

So if the row value is 2 then in period 4 it will equal 8 and in period 5 it will equal 10 and so on.  What DAX expression can I use to get the current Column value of a matrix in order to use that value to mutliply it by a row value.  Here is some sample test data


TABLE1            
CategoryAmountMonth          
A51          
B102          
C45          
D67          
             
             
MATRIX            
 123456789101112
A51015202530354045505560
B 2030405060708090100110120
C    2024283236404448
D      424854606672
1 ACCEPTED SOLUTION

Accepted Solutions
BIAB Regular Visitor
Regular Visitor

Re: Calculating row value by Column value

@Greg_Deckler  thanks for taking the time to reply.  I was able to accomplish what I needed by wrapping the max inside calculate and putting it in the right context.

VAR __period =
                CALCULATE (
                    MAX ( ACCOUNTING_PERIODS[ACCOUNTING_PERIOD_ID] ),
                    USERELATIONSHIP ( ACCOUNTING_PERIODS[YearMonthNo], 'Date'[YearMonthNo] )
                ) 
4 REPLIES 4
Super User
Super User

Re: Calculating row value by Column value

Just use MAX([Column]) to get the current value of your column in your matrix.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


BIAB Regular Visitor
Regular Visitor

Re: Calculating row value by Column value

Hi @Greg_Deckler 

Thats what I had but the MAX(TABLE1[Month]) is giving me that max period for the row.  For example with MAX(TABLE1[Month]) from the example above, I get 1 for the Column that is then multiply by value 5 of row A .  But I need to be able to multiple the value 5 of A by 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, and 12.  Not sure If I explained myself correctly. 

BIAB Regular Visitor
Regular Visitor

Re: Calculating row value by Column value

Capture 1507.PNG

BIAB Regular Visitor
Regular Visitor

Re: Calculating row value by Column value

@Greg_Deckler  thanks for taking the time to reply.  I was able to accomplish what I needed by wrapping the max inside calculate and putting it in the right context.

VAR __period =
                CALCULATE (
                    MAX ( ACCOUNTING_PERIODS[ACCOUNTING_PERIOD_ID] ),
                    USERELATIONSHIP ( ACCOUNTING_PERIODS[YearMonthNo], 'Date'[YearMonthNo] )
                )