Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hola my fellow Geeks,
Looking for some help if you could kindly sacrifice a bit of your time to help me out with a DAX measure in Excel environment please:
Below is the example I have, you can see two measure below each other:
1. NetValue:=sum(Value)
2. MsrAvgX:=AVERAGEX(VALUES('JCTRANS ODBC'[FY-MMM]),CALCULATE([NetValue]))
The purpose of the second measure is to show summarized numbers by month but once we get to a higher level of FY category/dimension I want it to show Average. But here comes the tricky part, not just any average..
Currently its showing 1676/7 =239 however I would want it to divide the total 1676 by 12 and instead of 7 (number of months that so far exists within the year).
Also if I was in current Year with only so far 4 months elapsed but there is data only against 2 months I would still want the Annual data for the FY to divide the figures accumulated during the FY by 4 months.
Any help with achieving that would be greatly appreciated.Cheers!
Solved! Go to Solution.
Ok took me a while but found another solution that works in Excel where I utilised the function ISFILTERED in the absence of ISINSCOPE. Below is how I've done it:
YrAvg:=if(ISFILTERED('Calendar'[FYmonth]),[NetValue],[NetValue]/[TestingVar])
where NetValue is just a simple sum of my values column
TestingVar represents getting the denominator right which was meant to be max month in Current Financial Year and 12 for closed FYs.
For that here is a bit of workaround which could be streamlined to look better:
Created 2 measures:
is before Fy22:=CALCULATE(MAX('JCTRANS ODBC'[Month]),FILTER(ALL('JCTRANS ODBC' ),VALUE(RIGHT('JCTRANS ODBC'[FY],4))<2022))
is in fy22:=CALCULATE(MAX('JCTRANS ODBC'[Month]),FILTER(ALL('JCTRANS ODBC' ),VALUE(RIGHT('JCTRANS ODBC'[FY],4))=2022))
And then these where reference in a Variable measure called TestingVar:
TestingVar:=VAR
VAR1 = [is before Fy22]
VAR
VAR2= [is in fy22]
return
if(MAX([FinYear])=2022,VAR2,VAR1)
Ok took me a while but found another solution that works in Excel where I utilised the function ISFILTERED in the absence of ISINSCOPE. Below is how I've done it:
YrAvg:=if(ISFILTERED('Calendar'[FYmonth]),[NetValue],[NetValue]/[TestingVar])
where NetValue is just a simple sum of my values column
TestingVar represents getting the denominator right which was meant to be max month in Current Financial Year and 12 for closed FYs.
For that here is a bit of workaround which could be streamlined to look better:
Created 2 measures:
is before Fy22:=CALCULATE(MAX('JCTRANS ODBC'[Month]),FILTER(ALL('JCTRANS ODBC' ),VALUE(RIGHT('JCTRANS ODBC'[FY],4))<2022))
is in fy22:=CALCULATE(MAX('JCTRANS ODBC'[Month]),FILTER(ALL('JCTRANS ODBC' ),VALUE(RIGHT('JCTRANS ODBC'[FY],4))=2022))
And then these where reference in a Variable measure called TestingVar:
TestingVar:=VAR
VAR1 = [is before Fy22]
VAR
VAR2= [is in fy22]
return
if(MAX([FinYear])=2022,VAR2,VAR1)
Thanks amitchandak, I will check the 2nd option as that seems to have dynamic value in the denominator depending on the existing months up to date within each year.. and let you know the outcome..
@sliceNdiceUup , Try like
if(isinscope('JCTRANS ODBC'[FY]), [NetValue]/12, [NetValue])
or
if(isinscope('JCTRANS ODBC'[FY]), [NetValue]/month(max('JCTRANS ODBC'[FY])), [NetValue])
Sorry I forgot to mention that I am working in Excel 365 where ISINSCOPE formula doesn't seem to be available.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
101 | |
78 | |
76 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |