Desperately need help! Avg tickets for each hour for selected days of week in a selected date range
I have been trying every possible option for days now and I am just going around in circles. I have a Date table, a Time table, and a data table. My data table contains one ticket per row with a timestamp. I have broken out the time stamp into date, time, weekday and hour. I have a day of week slicer that is connected to my date table ('Date'[Day of Week]) and a date slicer that is connected to my date table ('Date[MMYY]). These are the measures that I am using: Total Tickets = Countrows('Mytable') Avg Per Day = AVERAGEX(DISTINCT('MyTable'[Date Created]),Calculate(Countrows('MyTable')/[Count Days])) Count Days = DISTINCTCOUNT('MyTable'[Date Created]) Hourly Average = AVERAGEX(SUMMARIZE('MyTable', 'MyTable'[Hour],"Hourly Total",COUNTROWS('MyTable')),[Hourly Total]) The Hourly Average is where I am struggling. The hourly average measure is actually giving me the totals per hour for the selected day where I need the hourly AVERAGE of tickets per hour of each day (for the slicer selected day(s) and Month(s). I tried Hourly Average = AVERAGEX(SUMMARIZE('MyTable', 'MyTable'[Hour],"Hourly Total",COUNTROWS('MyTable')),[Hourly Total])/[Count of Days] and that gives incorrect averages that do not add up. As a test, I tried manually adding in the count of days. and this works. The averages per hour across the days is correct. So All Saturday and Sundays in Jan, Feb, and March = 25. Hourly Average = AVERAGEX(SUMMARIZE('MyTable', 'MyTable'[Hour],"Hourly Total",COUNTROWS('MyTable')),[Hourly Total])/25. Here's what I get in excel and what I am expecting to see - How do I get this measure to work using the slicer selected days?Calculating Average of a measure within a hierarchy
Hi everyone, I need to solve an issue that I am having with DAX, but I cant find the right query. To give you some context: I have different regions in my data that have the values T100, T200, T300 and so on. They can be found in the column RegionLevel2 in the table Geography. Then , in the same table there is a column called TerrLevel1 that has the territories. Each region has multiple territories. So for example the territories for region T100, are T101, T102, T103 and so on. For each of these territories I have calculated a Score, based on their individual sales. It is a measure called Score1 and working fine. Now I need to calculate the score for the regions. This score is based on the average of the sales of a regions territories. So in the case of T100, its score would be the average of the Score1 of T101, T102, T103 and so on. I have tried these DAX AVERAGEX(Geography, [Score1]) AVERAGEX(all(Geography[TerrLevel1]), [Score1]) AVERAGEX(VALUES(Geography[TerrLevel1]), [Score1]) CALCULATE( AVERAGEX(Geography, [Score1]), ALL(Geography[TerrLevel1])) but it is not giving me the right result. I guess i need to filter somehow to group the right territories with their region but I cant figure out how. I would appreciate any kind of help. Thank!
calculate max and min of a measure
hi everyone I have created a measure that calculated the achievement % . I'm trying to create max, min measure for that column for each sales agent and then find the average achievment% for all categories for the agent. reference to the example below: from the measure [new achiev%], i want to create maximum achievement measure and return 92% and minimum achievement measure return 61% to return and then the average achievement measure to return 73.19% what i'm trying to calculate from these measures is the sales agent incentive. the rules for the measure calculation is: -at least one category has achievment % of 90% -the achievement in all other 3 categories should be at least 70% in the example provided, agent won't get his incentive because he achieved 92% in one category and achieved below 70% in other categories.
