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.Excel Formula into DAX Formula
Hi All, I am trying to set up a formula in DAX but not sure where to start. I have this on excel but need to translate this to DAX format for powerbi. So a bit of background. I am trying to understand the percentage completion for a current project. For example the team have to go from 6000 (baeline) to 1150 (Target) for them to completed the acitvity. Therefore when the team hit 1150 they wouldve completed 100%. So i did an interpolation formula on excel as i wasnt sure how to do this on DAX. The formula is (-100(Current No - Target No)/(Baseline-Target No))+100. Can someone help me. RU
Compare rows from other table to make calculated column in first table
Hi, I have two tables, one with accounts, and one with expenses. In the table with accounts I have an ID, which is linked to the expense table, and I have an account Name, a status and I would like a calculated column which should determine whether or not the account is ready to be closed. Table with accounts: Id Account Status ShouldClose 1 A Active 0 2 B Completed 0 3 C Completed 1 4 D Completed 1 5 E Closed 0 This calculated column, marked with orange should be calculated based on the status and on the second table with expenses. If the current Actual and Posted Revenue and Expense are equal to each other, and the Account = Completed, then the flag should be 1. AccountID Type IsCurrent Rev Exp 1 Actual 1 500 100 1 Posted 1 500 100 2 Actual 1 600 150 2 Posted 1 600 50 3 Actual 0 1000 1000 3 Posted 0 500 500 3 Actual 1 1200 400 3 Posted 1 1200 400 4 Actual 1 1600 800 4 Posted 1 1600 800 5 Actual 1 900 100 5 Posted 1 900 100 E.g. for AccountID 3: Current Rev: Actual 1200 - Posted 1200 = 0 Current Exp: Actual 400 - Posted 400 = 0 Here the flag should be 1, because the Account is Completed. E.g. for Account 2: Current Rev: Actual 600 - Posted 600 = 0 Current Exp: Actual 150 - Posted 50 = 100 Here the flag should be 0, since both are not equal to 0. I hope this makes sense, and that you can help me 🙂
