Get the average of a column value based on total count of values in ID column
Hi, In a table i have column called "Object" which runs multiple times in a day and that runtime is captured in "StartTime" & "EndTime" columns, and the difference between the start and end time is captured in the "Duration" Column. Each object will be having multiple runid's which is captured in "RunID" column which is a unique value. For eg: Object AAA has two runid's called "111,222" and Object BBB has one runid called "333". Now i would like to get the average of each duration by count of that particular runid which is captured in "New Duration" column. For eg: The count of 111 runid is '10'. so for all those durations with runid 111 should be divided by the 10. The count of 222 runid is '6'. so for all those durations with runid 222 should be divided by the 6. The count of 333 runid is '8'. so for all those durations with runid 333 should be divided by the 8. Sample screenshot for above query: So since my Duration is "10" and total count of runid (111) is 10. So 10/10=1 that is my newduration.Similarly for all durations with runid's 111 should be divided bt 10. How to achieve this using DAX query?? I am connecting to this table in powerbi via SQL direct query mode. Thanks.Sameperiodlastyear is giving me the total sales of last year instead of for the given time period
Hello Community, Hope your all well. So im currently working on income report for my company. I have calculated the total income and would now like to calculate the total income for the same period last year.Where my problem lies in is the fact that whenever I try to do the dax measure sameperiodlastyear it gives me the total income of last year instead of narrowing it down to the date filter I have selected. For reference my total income is calculated using the following measure: Deckunsbeitrag = SUMX( 'BES DE KOELN$VK-Umsatz Details', 'BES DE KOELN$VK-Umsatz Details'[DB (MW)] ) The sameperiodlastyear measure is the following: Deckunsbeitrag LY = CALCULATE( [Deckunsbeitrag], SAMEPERIODLASTYEAR(Datumstabelle[Date]) ) I then have a date filter which I put into a slicer on the dates ranging from the 01.01.21 to the 10.03.21.Like I previously explained the sameperiodlastyear measure takes the total income of the whole year instead of looking for the total income in the time period of 01.01.20 to the 10.03.20. Again for reference I do have a date table and use it so I'm very confused as to why I'm getting the total income of last year instead of getting it fo the given time period that I filter on on that page. Any help would be greatly appreciated and thank you all a lot .
RLS path
I am pretty new to RLS and have been trying to implement it in a certain way. My table above is a sample. I am trying to create RLS here based on the levels. Normally I can create 3 roles for 3 levels where, Level 1 is regular employees Level 2 is supervisor of employees Level 3 is the manager/head of the building (based on the BuildingID) I am trying to create RLS where the manager (Level 3) will be able to see the data for people below them by filtering through. For example when the manager of BuildingID='233' logs in, they can filter through the report to see people they are assigned to. It would be clickable in the filter pane. However, I am more used to on doing DAX calculations when the manager ID is on a different column and this seems to be a new scenario. I have tried a couple of methods and so far have not been successful. Any kind of help here would be much appreciated.Counting ELA and MATH
I have an issue and am having trouble wrapping my head around it. I am trying to count students absent in English and Math classes. The issue here is the database places students absent per period on a row to row basis. I have created a sample here. The original one is over a million rows. I was wondering if there is any way I can create a column that counts number of students absent in MATH and ELA. I know I can do table. But I need to be looking more like the picture below (red font is where I want the calculation) and was wondering if someone could help.Select a measure based on filter page selection
Hi community, I would know if exist the method to select a dinamically measure with link on filter page. At below an example: I have a one filter page e.g "brand company" and how to select the modality, the KPIs switch in dyanamically mode. The widget is a Card I think this type of approach but it doesn't work: Measure_Final = IF ((FILTER_1) = "A"; Measure_1; IF(FILTER_1) = "B"; Measure_2; IF(FILTER_1) = "C"; Measure_3; "Measure_Overall") For more details i have upload a screenshot Thanks advance for your support, Best regard
Can I create a new table based on a measure?
I have a data set with person number, week number, and hours worked for that day. I'm trying to calculate the average number of hours per week each employee worked, and then be able to group the COUNT of personIDs with the same number of average number of hours worked. Essentially answering the question, how many employees worked an average of 8 hours per week. I was able to calculate the average number of hours per week per employee, but when I tried to map it to a table with count of person ID it did not work at all. My calculation for average hours per weeks looks like this... AvgHrPerWeek = ROUND(AVERAGEX( KEEPFILTERS(VALUES('Table'[WeekNumber])), CALCULATE(SUM('Table'[HoursWorked]))),0) When placed in a table with PersonID this works great as it calculates each average hours per PersonID. The next step is the tricky one where I want to essentially take that PersonID and calculated measure of Average Hours and then calculate how many PersonIDs have the same AverageHours and map that on a graph with AverageHours on the horizontal axis and PersonID on the vertical. PersonID WeekNumber HoursWorked 0023 1 8 0023 1 6 0023 4 9 0023 4 4 0023 6 2 0023 6 6 0023 6 6 0023 6 3 0025 1 8 0025 1 6 0025 4 4 0027 4 2 0027 4 5 0029 2 7 0029 2 8 0029 6 2DAX Help on Custom Quarters Calculation
Hi Expert, I am stuck one of the requirements, I have a file which has quarterly data, however, the output column needs only last date of each quarter to be updated as a value for respective quarter. Date Total QTR Final Value (output) Thursday, August 1, 2019 4898445864 2019-Q4 4432185600 Friday, August 2, 2019 5007203158 2019-Q4 4432185600 Wednesday, October 30, 2019 4362185600 2019-Q4 4432185600 Thursday, October 31, 2019 4432185600 2019-Q4 4432185600 Friday, November 1, 2019 4919064058 2019-Q1 4362185600 Saturday, November 2, 2019 4898445864 2019-Q1 4362185600 Tuesday, December 31, 2019 4362185600 2019-Q1 4362185600 Wednesday, January 1, 2020 4362185600 2019-Q1 4362185600 Thursday, January 2, 2020 4362185600 2019-Q1 4362185600 Friday, January 3, 2020 4362185600 2019-Q1 4362185600 Saturday, January 4, 2020 4362185600 2019-Q1 4362185600 Saturday, February 1, 2020 4362185600 2019-Q2 5562185600 Sunday, February 2, 2020 4919064058 2019-Q2 5562185600 Thursday, February 27, 2020 4779189458 2019-Q2 5562185600 Sunday, March 1, 2020 4887849072 2019-Q2 5562185600 Tuesday, April 28, 2020 4362185600 2019-Q2 5562185600 Wednesday, April 29, 2020 4362185600 2019-Q2 5562185600 Thursday, April 30, 2020 5562185600 2019-Q2 5562185600 Friday, May 1, 2020 4362185600 2019-Q3 6562185600 Saturday, May 2, 2020 4362185600 2019-Q3 6562185600 Sunday, May 31, 2020 4779189458 2019-Q3 6562185600 Monday, June 1, 2020 4939260310 2019-Q3 6562185600 Thursday, July 30, 2020 4362185600 2019-Q3 6562185600 Friday, July 31, 2020 6562185600 2019-Q3 6562185600 Please help me. Attached is the date for your reference. https://drive.google.com/open?id=1WKQaoZIGDoYf-UyWaTt_Aua3U9bidiMs Thanks
