Forum Discussion
Helper IHelp creating dax measure
Hi anna-lee
Please create a measure:
Measure = VAR __Year = MAX('Table'[Year]) VAR __Revenue = SUMX(FILTER('Table',[Description] = "revenue" && 'Table'[Vendor Code] = SELECTEDVALUE('Table'[Vendor Code])),[Value]) VAR __AR = SUMX(FILTER('Table',[Description] = "accountsreceivable" && 'Table'[Vendor Code] = SELECTEDVALUE('Table'[Vendor Code])),[Value]) VAR __ARPY = SUMX(FILTER(ALL('Table'),[Description] = "accountsreceivable" && [Year] = __Year - 1 && 'Table'[Vendor Code] = SELECTEDVALUE('Table'[Vendor Code])),[Value]) VAR __Result = DIVIDE(__Revenue, (__AR - __ARPY) / 2) RETURN __ResultThe result in matrix is the same as the result in excel:
Best regards,
Yadong Fang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper IGreg_Deckler Sorry, I missed updating the value portion, but now there's error in the syntax.
Community Championanna-lee For your first squiggly red line right where it starts, put a ) right before the ,
- anna-lee
Greg_Deckler syntax fixed thank you but still no values pulling through
Measure =VAR __Year = MAX('Table'[Year])VAR __Revenue = SUMX(FILTER('Table','Table'[Description] = "revenue"),'Table'[Value])VAR __AR = SUMX(FILTER('Table','Table'[Description] = "accountsreceivable"),'Table'[Value])VAR __ARPY = SUMX(FILTER('Table','Table'[Description] = "accountsreceivable" && 'Table'[Year]= __Year - 1),'Table'[Value])VAR __Result = DIVIDE(__Revenue, (__AR - __ARPY) / 2)RETURN__Result- Greg_Deckler
anna-lee I've attached the PBIX I am using where it is working. It is Table5 and Measure. There is a matrix visual on the first page. Attached below signature.
- anna-lee
Greg_Deckler thank you for sharing pbix file! However, the values I've calculated in excel are not in line with what is populating in the matrix.
2016 2017 2018 2019 2020 2021 MA33C4 5.31 2.18 -6.82 -0.57 7.55 10.84 RQ020G 9.83 16.56 25.16 -8.01 0.00