DAX Sum if Date, Time table, and Activity between 3 different tables
Hello Dears, I've the below data set and I want to create a measure that calculate total number of trips with the below critera: - Sum 'DR_DUTIES'[# Of Trips] If 'ROSTER DATABASE'[DATE] = 'APP_TT'[Date] & 'APP_TT'[Applied TT] = 'DR_DUTIES'[TimeTable] & 'DR_DUTIES'[Driving Duty] = 'ROSTER DATABASE'[Activity] Also, I need to create another measure with the same critera but to Sum 'DR_DUTIES'[Driving Hours] noting that this column is Time formatted [hh:mm:ss AM/PM]. Thanks in advance!
DAX Sum or Max value greater than input value(slicer)
Hi, I have table columns sublock,elementpath, value(integer). sublock and elementpath forms unique key. i have input slicer to enter scalar value integer value like 10, 44.input slicer sublock has values 1a,1b,1c to 1s, 2a,2b,2c to 2s, 4a,4b,4c to 4s i created running total measure (RT_M)for input column value. if user selects sublock slicer 4F. then report visual starts from 4F.i created distance measure from input slicer it will generate distance values(ex: 4f 0, 4g 1,4h 2) so i can sort visual by distance.If user enters 50 as input,i need to get max running total or sum up to the block. example in below screen shot for input 50 , running total greater than or equal to input value(50) reaches at block 4H. I need to get sum value up to block 4H(from 4F to 4H) that is 54.47. I created measure that is working correct 80 percent of time , somet times its giving incorrect value. please help. thanks for ur time. Below is the measure. EndingMW_M = var CurrDistance = [TargetMW_Dis] var result = Calculate(SUM('EEP vwMWValue'[Value]),Filter(ALL('EEP vwMWValue'),'EEP vwMWValue'[D_I_M] >=0 &&'EEP vwMWValue'[D_I_M]<= CurrDistance)) RETURN result in above [TargetMW_Dis] is measure max distance from block 4F to 4 H that is 2. D_I_M is measure to calculate distance from slicer selection block(4F)Tricky Situation with SUM
Here is the Data I am using: Type Spent Returns Apple 100 Orange 50 200 Banana 25 150 Pear 500 Kiwi If I use ROI = sum(Data[Returns]) / sum(Data[Spent]) I get the following Information on Data: the spent and returned data is filled in by the user and will remain blank until then. So will have to create DAX measures that will assign 0 where there is a blank. However, if we do that, we get Infinity and NaN for Pear or Apple. So we have to HardCode such incidents to 0. Questions 1) I also want to show the Kiwi line item as well and assign 0 to the ROI column for Kiwi. 2) How to deal with Infinity, show 0 instead of Infinity. 3) Result in either 1 of the shown expected output will work Any suggestions, tips, and tricks are welcome. I want to get the following output along with totals at the end for the first two columns and the average for the third column, shown below: Expected Output Type Spent Returns ROI Apple 100 Orange 50 200 4 Banana 25 150 6 Pear 500 Kiwi Total 175 850 5 OR Type Spent Returns ROI Apple 100 0 Orange 50 200 4 Banana 25 150 6 Pear 500 0 Kiwi 0 Total 175 850 5
table visual summing total wrong
im trying to calculate commission by each agent according to pack he sold and number of transations by pack, the problem is im when im multiplying the pack commission by number of transactions it's doing the same multiplication forthe overall total transactions and the overall commssion tota which which is resulting in wrong total commission for the agent. in the screenshot the total commission should be 124.305 instead of 3908.21 the measure im using: CALCULATE(COUNT('categories trans'[revenue]) * SUM('categories commision'[Commission]), GROUPBY ( 'categories trans', 'categories trans'[AGENT_NAME], 'categories trans'[Categoty], 'categories trans'[Commitment], 'categories trans'[BASE_TYPE], 'categories trans'[revenue] )) note: the data model consists of two table : trancations table by agents and commission pack by category
