Writing Measure that Returns Customer Specific Revenue based on Customer Specific Multiplier
Hello, I am attempting to write a measure that examines the customer name before returning a customer specific revenue given a customer specific multiplier multiplied by the amount of items sold. For example, if the customer is customer 1 in this case, then we would take the customer 1 specific multiplier and multiply it by the number of items sold to get the customer 1 specific revenue (if customer = customer 1, then customer 1 multiplier * # of items sold = customer 1 revenue). However, the measure does need to be able to store multiple (up to 30) different customer specific multipliers and in turn know when to use each one based on filtering from a slicer that allows you to choose between customers. I have written the following measure that only appears to work for customer 1 and not the following customers in this example. The numbers 5, 10, and 15 are placeholder customer specific multipliers. Measure1 = SUMX (‘Table’, IF(‘Table’[Customer]= “Customer 1”, ‘Table’[Items Sold]*5, IF((‘Table’[Customer]= “Customer 2”, ‘Table’[Items Sold]*10, IF((‘Table’[Customer]= “Customer 3”, ‘Table’[Items Sold]*15 ….. Here is another measure I’ve attempted but have had no luck with: Measure2 = SWITCH( TRUE(), ‘Table’[Customer] = “Customer 1”, ‘Table’[Items Sold]*5, ‘Table’[Customer] = “Customer 2”, ‘Table’[Items Sold]*10, ‘Table’[Customer] = “Customer 3”, ‘Table’[Items Sold]*15, )) Any feedback and help are much appreciated.
IF Statement Wasting Calculation Time on Measure When Condition NOT Met
Hello, I have come across a curious issue and was looking for some guidance/help. I am working on some financial data that is being given to me in a way where I am having to create a who lookup table using PATHITEM type commands to build a hierarchy. Because of this measures do take a little time to compute (about 8-10 seconds for a couple million records). So, as I move to create projections based on past year averages of values, I am attempting to create an IF or SWITCH structure that reduces the number of calculations down to the lowest possible amount to save visual refresh time by leveraging some toggle switches on the report page for Budget or Trend and a toggle to change the number of past years considered in creating the forecasted average. I found a curious issue though. If I create a very simple: IF( calc_type = 1, 1, 2, 2 ) The compute time is obviously instant. But, if I set the toggle that controls calc_type to 2 and change the code to: IF( calc_type = 1, [do measure], 2, 2 ) I would expect that the return would still be almost instant because the calc_type is not 1, therefor the measure should not calculate but in fact the measure does calculate because now it takes 8-10 seconds to output the number 2. Why is time being wasted by the engine to calculate a measure when the condition to evaluate the measure has not been met? I did find that when I reverse the order and put the 2 condition above the 1, the output is instant again. But if I leave the conditions flipped (2 then 1 instead of 1 then 2) and add both measures: IF( calc_type = 2, [do measure 2], 1, [do measure 1] ) It takes twice as long to evaluate the output which tells me that indeed both measures are being evaluated even though the condition for 1 is not met. Does anyone know why this is happening or have any tips to ensure that 1 and only 1 measure is being calculated instead of calculating those where the condition is not met? Thanks!
IF statement returns 0%
Hi guys, My previous colleague wrote an Dax If statement to return a percentage of a number. Below you can see the dax statement The problem is that I don't understand this statement. Dax If statement: " ProductivityA = IF([InvoicingTargetInHours] - [NormWorkingHours] = 0, 0, IFERROR(([InvoicingTargetInHours] / [NormWorkingHours]), 0)) " The measure is calculating the percentage correct if the InvoicingTargetInHours and the NormWorkingHours are different, see picture below: But once the InvoicingTargetInHours and the NormWorkingHours are the same number, it return 0% when in fact it should return 100% as productivity, see picture below: Can someone help explain this statement to me and how I should fix this dax? Should I rewrite or not? Im clueless at this point. Thanks guys!Not picking up all IF clauses in new column but does in Measure
Hi all, I am having an issue with IF. I created a measure that works correctly: Attainment Standing = IF([Project Milestone Attainment] >= .9, "Excellent", IF([Project Milestone Attainment] >= .7 && MBIS_PROJ_MILESTONES_V[Project Milestone Attainment] < .9, "Good",IF([Project Milestone Attainment] < .7, "Needs Improvement"))) I created a coulmn that does not: Attainment = IF([Project Milestone Attainment] >= .9, "Excellent", IF([Project Milestone Attainment] >= .7 && MBIS_PROJ_MILESTONES_V[Project Milestone Attainment] < .9, "Good",IF([Project Milestone Attainment] < .7, "Needs Improvement"))) It is not identifying "Good" in the column. Need for Legend or DetailsIf contain two values then return
Hi Forum, I am here again to ask help, Now i have a table with columns like below: PartNo Step Status AAA Step1 In time AAA Step1 Delay AAA Step2 Completed BBB Step1 In time BBB Step1 Completed And i want to count the number of "Delay" and "In time" per PartNo and per Step. But the difficuly is i want: if status for the same PartNo and the same Step contain "Delay", then just count "1" for Delay, and if status for the same PartNo and the same Step contain "Completed" or "In time", then count "1" for In time. For example, for the table i have here, the result of count will be: PartNo Step Count_Delay Count_InTime AAA Step1 1 AAA Step2 1 BBB Step1 1 I try to use the Measure solution below, but it doesn't work well. And i don't understand why. I create a measure to return one Status per part StatusPerPart = var calc=CALCULATE(DISTINCTCOUNT('Table 2'[PartNo]); filter('Table 2'; 'Table 2'[Status]="Delay")) Return If(calc=1;"Delay";"In time") After, i create a new measure to count the Status, Count_InTime = countrows(filter(Table 2;Table 2[StatusPerPart]="In time")) Count_Delay = countrows(filter(Table 2;Table 2[StatusPerPart]="Delay")) Thank you!
DAX Measure : SUMIF Next Sale Month not equal to Transaction Month
Hello, I have three tables Inventory Id 0001 0002 0003 Sales Inventory Id Sale Date Quantity 0001 10/01/20 15 0002 10/02/20 10 0003 10/03/20 20 0001 10/04/20 5 0002 09/02/20 5 0003 10/05/20 20 0001 11/11/20 15 Transaction Log Inventory Id Transaction Date 0001 09/30/20 0002 09/29/20 0003 10/01/20 0001 10/02/20 0002 08/31/20 0003 10/01/20 0001 10/31/20 Here are my relationships Inventory Sales Id 1:* Inventory ID Inventory Transaction log Id 1:* Inventory ID How do I write a measure that will give me the total quantity sold where the transaction month is not equal to the following sale month? I want the result to be this: Inventory Id Quantity Transaction Date Sale Date 0001 15 09/30/20 10/01/20 0002 10 09/29/20 10/02/20 0002 5 08/31/20 09/02/20 0001 15 10/31/20 11/11/20 Thank you in advance for any help or guidance!!
