Trying to create a simple measure of Projects per Staff numbers
Sorry, this is first time posting. I am a self taught PowerBI dashboard creator and I am trying to create a simple 'projects per staff number' calculation for my companies dashboard. I feel it should be easy but the numbers keep being wrong, any help would be greatly appreciated, it's been quite frustrating. Details below. The Data: I have two sources of data. A Direct Query into a SQL database (DATA1) that lists all projects, with columns for Department etc. and a spreadsheet (DATA 2) with a column for department and staff numbers. The solution: I want to create a new measure of projects per staff numbers (PPP) and have tried creating a new measure under the spreadsheet data source with code along the lines of: Projects per Person (PPP) = VAR Depart_Project_Count = COUNTAX(FILTER(DATA1[ProjectType]="PROJECT"),DATA1[Department]) VAR Staff_Count = COUNTAX('DATA2','DATA2'[Department]) RETURN ( Depart_Project_Count / Staff_Count ) No errors are produced when I run it but gets the numbers wrong, see table belwo: For Glasgow the number PPP should be 3.14 ish (44/14) no 22. Any help or pointers would be greatly appreciated. Thanks
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.
divide function returns 0 instead of number
hello i have a measure where im dividing two measures numinator : [active users 30 days ago]-[active users prevoius 30 days ago] denominator :[ active users 30 days ago] both measures im using return values seperately and when subtracting also, but after using the divide function it returns zero the DAX code for active users 30 days ago: CALCULATE(distinctCOUNT(table[user_id]),DATESBETWEEN('Calender'[Date], MAX('Calender'[Date]) -30, MAX('Calender'[Date]))) DAX code for active users previous 30 days ago: CALCULATE(distinctCOUNT(table[user_id]) ,DATESBETWEEN('Calender'[Date], MAX('Calender'[Date]) -60, MAX('Calender'[Date]) -30))) ) DAX for the division calculation= MOM= calculate(divide( [active users 30 days ago]-[active users prevoius 30 days ago],[active users prevoius 30 days ago])) the results im getting:QUOTIENT rerurns 0 when it should not
Hi all, I have a problem with DAX functions QUOTIENT. I have "minutes spent fact" column and I want to convert it to "X day, Y hours, Z mins" format. My first step is to divide "minutes spent fact" by 60 to convert minutes to hours I'm using QUOTIENT function. Here what it returns QUOTIENT (1320, 60) should return 22. Am I right? or I get the wrong idea of this function... Here are other screen with some wrong zero resultsSimple Division driving me crazy!
Hi all I just can't seem to understand why my tabular model is coming up with the wrong answer. All I'm doing is dividing one column by the other. ='Stock Analysis'[Physical Quantity (R)]/[Net Usage] I didn't need to use variables but I've tried lots of different ways to divide it but it keeps coming up with the same problem. 19.77686 / 0.014370 The answer is 1,978 not 1,376. Note; the first column Physical Quantity (R) is a hard number created via power query. The second number is a calcuated column which I'll include but I don't see how this should have an affect on this simple division: Net Usage='Stock Analysis'[Consumption Quantity 12 Months]+'Stock Analysis'[Sold Quantity 12 months] Consumption Quantity 12 Months=[Pick Quantity In Previous 12 Months] Pick Quantity In Previous 12 Months:= VAR CurrentDate = TODAY() VAR PreviousDate = Date(Year(CurrentDate),MONTH(CurrentDate)-12,DAY(CurrentDate)) VAR RESULT = CALCULATE( SUM([QtyReporting]), FILTER( 'Primary Date Table', 'Primary Date Table'[Date] >= PreviousDate && 'Primary Date Table'[Date] <= CurrentDate ) ) RETURN RESULT Thank you!
