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.
Hi All,
I have written a measure in which the average is calculated for days, months, quarters as well as years but while drilling up to weeks the average is not being calculated. The following is the measure
Recevied Amount calc1 =
var MinDateselected =
CALCULATE(
MIN( 'Date'[Date] ),
ALLSELECTED( 'Date'[Date] )
)
var MaxDateselected =
CALCULATE(
Max( 'Date'[Date] ),
ALLSELECTED( 'Date'[Date] )
)
var Days = DATEDIFF(
MinDateselected,
MaxDateselected+1,
DAY)
VAR _receAmt = CALCULATE(SUM(Payments[ReceivedAmount]),FILTER('Date','Date'[Date] >= MinDateselected && 'Date'[Date]<=MaxDateselected))
Return IF(AND(ISFILTERED('Date'[Date]),(ISBLANK(_receAmt) && MIN('Date'[Date]) >= MinDateselected && MAX('Date'[Date]) <= MaxDateselected)),0, _receAmt)
Thanks,
Prema
Solved! Go to Solution.
@PremaBoddeda , You need try a measure like
Daily = averageX(Values(Date[Date]),calculate(Sum(Table[Value])) )
Monthly= averageX(Values(Date[Date]),calculate(Sum(Table[Value])) )
Then have a measure, in the reverse order of drill, the lowest level first
Switch(True(),
isinscope(Date[Date]), [Daily],
isinscope(Date[Month Year]), [Monthly],
// Add other
[Yearly]
)
IsInScope - Switch Measure at different levels: https://youtu.be/kh0gezKICEM
https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/
@PremaBoddeda , You need try a measure like
Daily = averageX(Values(Date[Date]),calculate(Sum(Table[Value])) )
Monthly= averageX(Values(Date[Date]),calculate(Sum(Table[Value])) )
Then have a measure, in the reverse order of drill, the lowest level first
Switch(True(),
isinscope(Date[Date]), [Daily],
isinscope(Date[Month Year]), [Monthly],
// Add other
[Yearly]
)
IsInScope - Switch Measure at different levels: https://youtu.be/kh0gezKICEM
https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |