Forum Discussion
JST15
1 year agoHelper I
Annualizing Data to Include in Matrix Table with History
Hi very smart folks! I have an underlying set of data that includes five years of historic data for volume. My boss wants a simply matrix table that shows volume by year plus a few other metrics. Th...
- 1 year ago
hI JST15
To evaluate calculation by year and sum them up, use SUMX on a virtual table.
My Measure = VAR MaxYear = CALCULATE ( MAX ( Dates[Year] ), ALLSELECTED ( Dates ) ) // Finds the max year in the Dates table based on current selection, use ALL to ignore slicer selections/filters on Dates VAR MaxMonthNumber = MONTH ( CALCULATE ( MAX ( Dates[Date] ), ALLSELECTED ( Dates ) ) ) // Finds the month number of the max date in the Dates table based on current selection RETURN SUMX ( ADDCOLUMNS ( SUMMARIZE ( Fact, Fact[Year] ), "@qty", IF ( Fact[Year] = MaxYear, DIVIDE ( [Qty Measure], MaxMonthNumber ) * 12, [Qty Measure] ) ), [@qty] )
danextian
1 year agoSuper User
Hi JST15
Are you trying to create a calculated table? SUMX returns a scalar value which is not a valid a table. That is supposed to be inputed as a measure. Add th year from your calendar table to a viz and then add this measure.
JST15
1 year agoHelper I
It's wortking. Thank you again!!!