Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BIAB
Resolver I
Resolver I

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

@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] )
                ) 

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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. 

Capture 1507.PNG

@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] )
                ) 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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