Hi all, I've been trying to figure this out for 3 days and it's driving me insane!! I appreciate your help more than you can imagine!
Summary: I'm trying to identify staff travel trends; they are grouped in 4 categories: traveled below 25% of weeks out of total within the defined dates in the filter; 25% to 50%; 50% to 75%; and above 75%.
I need to get to a point where I can say x% of my staff traveled below 25%, y% traveled 25% to 50%, etc.
In addition, I have a level column, and I need to show the split by level in a different graph.
Issue: I tried a summarized table, by staff and travel weeks count; it gave me all I needed perfectly, but it was static. I need to manipulate the date filter on the dashboard.
So I am trying to do this via a dynamic measure, and use the switch for the axis. (I created a table with the 4 categories).
- Problem 1: my first measure works when I have all the data by staff in a table/matrix visual, but gives blanks for card / graph visual.
first measure 1. Dynamic measure count _ Below 25% =
var summarizeweeks = SUMMARIZE('Travel Trends to use','Travel Trends to use'[Name - Active Report], 'Travel Trends to use'[Position],'Travel Trends to use'[Staff tenure at departure date],"Count of weeks",counta('Travel Trends to use'[Week starting]),"Count of distinct weeks",DISTINCTCOUNT('Travel Trends to use'[Identifier]))
VAR SelectedStartDate =calculate( MIN('Travel Trends to use'[Departure Date]),all('Travel Trends to use'[Name - Active Report]))
VAR SelectedEndDate = calculate( max('Travel Trends to use'[Departure Date]),all('Travel Trends to use'[Name - Active Report]))
VAR TotalWeeks = DATEDIFF(SelectedStartDate, SelectedEndDate, WEEK)
VAR TravelWeeks = sumx(summarizeweeks,[Count of distinct weeks])
VAR TravelersperLevel = calculate([Distinct staff count],GROUPBY('Staff Level Mapping','Staff Level Mapping'[Mapped level grouping IFS]))
VAR AvTravelWeeks = TravelWeeks/TravelersperLevel
VAR PercentageTravelWeeksInclAvByLevel = divide(AvTravelWeeks,TotalWeeks)
VAR summarizetravelers = CALCULATETABLE (SUMMARIZE('Travel Trends to use','Travel Trends to use'[Name - Active Report], "Staff count",DISTINCTCOUNT('Travel Trends to use'[Name - Active Report]),"% of travel",PercentageTravelWeeksInclAvByLevel))
Return
// sumx(summarizetravelers,[Staff count to use for below 25%])
COUNTROWS(
summarize(filter(summarizetravelers,[% of travel]<0.25),[Name - Active Report])
)
second measure (tried it a different way):
4. Dynamic measure count _ Above 75% =
var summarizeweeks = SUMMARIZE('Travel Trends to use','Travel Trends to use'[Name - Active Report], 'Travel Trends to use'[Position],'Travel Trends to use'[Staff tenure at departure date],"Count of weeks",counta('Travel Trends to use'[Week starting]),"Count of distinct weeks",DISTINCTCOUNT('Travel Trends to use'[Identifier]))
VAR SelectedStartDate =calculate( MIN('Travel Trends to use'[Departure Date]),all('Travel Trends to use'[Name - Active Report]))
VAR SelectedEndDate = calculate( max('Travel Trends to use'[Departure Date]),all('Travel Trends to use'[Name - Active Report]))
VAR TotalWeeks = DATEDIFF(SelectedStartDate, SelectedEndDate, WEEK)
Return
VAR TravelWeeks = sumx(summarizeweeks,[Count of distinct weeks])
VAR TravelersperLevel = calculate([Distinct staff count],GROUPBY('Staff Level Mapping','Staff Level Mapping'[Mapped level grouping IFS]))
VAR AvTravelWeeks = TravelWeeks/TravelersperLevel
VAR PercentageTravelWeeksInclAvByLevel = divide(AvTravelWeeks,TotalWeeks)
VAR _above75perc = CALCULATE(DISTINCTCOUNT('Travel Trends to use'[Name - Active Report]),FILTER('Travel Trends to use',PercentageTravelWeeksInclAvByLevel > 0.75))
Return
_above75perc
- Problem 2: my switch measure is not working, it's giving me the total number of staff in one category "Below 25%"
Dynamic measure count =
var summarizeweeks = SUMMARIZE('Travel Trends to use','Travel Trends to use'[Name - Active Report], 'Travel Trends to use'[Position],'Travel Trends to use'[Staff tenure at departure date],"Count of weeks",counta('Travel Trends to use'[Week starting]),"Count of distinct weeks",DISTINCTCOUNT('Travel Trends to use'[Identifier]))
VAR SelectedStartDate =calculate( MIN('Travel Trends to use'[Departure Date]),all('Travel Trends to use'[Name - Active Report]))
VAR SelectedEndDate = calculate( max('Travel Trends to use'[Departure Date]),all('Travel Trends to use'[Name - Active Report]))
VAR TotalWeeks = DATEDIFF(SelectedStartDate, SelectedEndDate, WEEK)
VAR TravelWeeks = sumx(summarizeweeks,[Count of distinct weeks])
VAR TravelersperLevel = calculate([Distinct staff count],GROUPBY('Staff Level Mapping','Staff Level Mapping'[Mapped level grouping IFS]))
VAR AvTravelWeeks = TravelWeeks/TravelersperLevel
VAR PercentageTravelWeeksInclAvByLevel = divide(AvTravelWeeks,TotalWeeks)
VAR _below25perc = CALCULATE(DISTINCTCOUNT('Travel Trends to use'[Name - Active Report]), FILTER('Travel Trends to use',[Travel weeks % category to use]="Below 25%"))
VAR _above75perc = CALCULATE(DISTINCTCOUNT('Travel Trends to use'[Name - Active Report]),FILTER('Travel Trends to use',[Travel weeks % category to use]="Above 75%"))
VAR _25to50perc = CALCULATE(DISTINCTCOUNT('Travel Trends to use'[Name - Active Report]),FILTER('Travel Trends to use',[Travel weeks % category to use]="25% to 50%"))
VAR _50to75perc = CALCULATE(DISTINCTCOUNT('Travel Trends to use'[Name - Active Report]),FILTER('Travel Trends to use',[Travel weeks % category to use]="50% to 75%"))
Return
SWITCH( SELECTEDVALUE('Travel weeks categories'[Travel weeks % categories]),
"Below 25%", _below25perc,
"25% to 50%", _25to50perc,
"50% to 75%", _50to75perc,
"Above 75%",_above75perc )
