Forum Discussion
Different measure per drill down level
I have a hierarchy with 2 levels Dates.Weeks and Dates.Days. I have 2 measures [Weekly Average] and [Daily Average]. I need a new measure to switch between [Weekly Average] and [Daily Average] based on the selected hierarchy level.
My problem occurs on Mondays when there is only one value for Dates.Weeks and Dates.Days.
I've searched and found the below suggestions, but this does not seem to work for my situation.
https://community.powerbi.com/t5/Desktop/Different-measure-per-drill-down-level/m-p/627225
Suggestions?
Hi Anonymous ,
We can try to create a measure as value field of chart to meet your requirement:
Measure = IF ( ISINSCOPE ( Dates[Days] ), CALCULATE ( [Daily Average] ), CALCULATE ( [Weekly Average] ) )We use ISINSCOPE function to verify current level and calculate different value
Best regards,
8 Replies
- amitchandak
Super User
Anonymous , Not sure I got it. But you can create Monday to Sunday week like
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2) - parry2k
Anonymous Can you explain the problem with sample data?
Weekly Average = CALCULATE(SUM(Sales.Sales),
DATESBETWEEN(Dates.Days,LASTDATE(Dates.Days)-34,
LASTDATE(Dates.Days)
)
) / 5Daily Average = CALCULATE(SUM(Sales.Sales),
DATESBETWEEN(Dates.Days,LASTDATE(Dates.Days)-34,
LASTDATE(Dates.Days)
)
) / 35Weeks Days Sales 3/23/2020 3/23/2020 110 3/23/2020 3/24/2020 111 3/23/2020 3/25/2020 112 3/23/2020 3/26/2020 113 3/23/2020 3/27/2020 114 3/23/2020 3/28/2020 115 3/23/2020 3/29/2020 116 3/30/2020 3/30/2020 117 3/30/2020 3/31/2020 118 3/30/2020 4/1/2020 119 3/30/2020 4/2/2020 120 3/30/2020 4/3/2020 121 3/30/2020 4/4/2020 122 3/30/2020 4/5/2020 123 4/6/2020 4/6/2020 124 4/6/2020 4/7/2020 125 4/6/2020 4/8/2020 126 4/6/2020 4/9/2020 127 4/6/2020 4/10/2020 128 4/6/2020 4/11/2020 129 4/6/2020 4/12/2020 130 4/13/2020 4/13/2020 129 4/13/2020 4/14/2020 128 4/13/2020 4/15/2020 127 4/13/2020 4/16/2020 126 4/13/2020 4/17/2020 125 4/13/2020 4/18/2020 124 4/13/2020 4/19/2020 123 4/20/2020 4/20/2020 122 4/20/2020 4/21/2020 121 4/20/2020 4/22/2020 120 4/20/2020 4/23/2020 119 4/20/2020 4/24/2020 118 4/20/2020 4/25/2020 117 4/20/2020 4/26/2020 118 4/27/2020 4/27/2020 119 4/27/2020 4/28/2020 120 4/27/2020 4/29/2020 121 4/27/2020 4/30/2020 122 4/27/2020 5/1/2020 123 4/27/2020 5/2/2020 124 4/27/2020 5/3/2020 125 - amitchandak
Anonymous , Can you redescribe your problem with data.
For the week you should use week rank to go in past refer my example
Or
