Forum Discussion
Calculated column incorrect calculation
Well, anything worth doing is worth over doing. Or something like that.
I started by writing some questionable code to generate data close to what you're working with:
var rand = new Random();
var data = Enumerable.Range(0, 50000)
.Select(x => {
var isLate = rand.Next(0, 20) < 2 ? 1 : 0;
return (month: CultureInfo.CurrentCulture.DateTimeFormat.MonthNames[rand.Next(0, 11)],
status: new[] { "On-Time", "Late" }[isLate],
late: isLate,
entry: 1);
})
.Select(x => x.month + "," + x.status + "," + x.late + "," + x.entry);
File.WriteAllLines(@"C:\temp\orders.csv", data);From there, I've got something pretty close to what you've got (just without the year/day parts of the hierarchy:
At this point, I'm going to want to create a Measure, not a new column:
With the exact formula you used, I can create that measure and see our expected results:
The cool thing is, now you can use that measure against any logical dimension; you can get your measure by Year/Salesperson/Client etc.
Hopefully this helps.
So, I don't know what happened with my excel sheet but the Entry column was actually a formula and it still is. But, I changed the data to a number. It was general at first.
I did end up changing my formula to a measure instead of a calculated column. I appreciate everyone's input. I was able to figure out that the original data was presumably the issue. I do have some other things I need to learn about this software. So, I will probably be a frequent flier. lol
This software seems to be very easy to use.
I don't know how to post more of this picture. But, it should give you an idea.
Promised Date = Date we should have had the order to their dock
Dock Date = Actual date the order hit customers dock
On-Time Vs Late = Simply formula Saying whether the order was on-time or late
Late = 0 for on time 1 for late
Entry = used to count home many rows there are.
Well, anything worth doing is worth over doing. Or something like that.
I started by writing some questionable code to generate data close to what you're working with:
var rand = new Random();
var data = Enumerable.Range(0, 50000)
.Select(x => {
var isLate = rand.Next(0, 20) < 2 ? 1 : 0;
return (month: CultureInfo.CurrentCulture.DateTimeFormat.MonthNames[rand.Next(0, 11)],
status: new[] { "On-Time", "Late" }[isLate],
late: isLate,
entry: 1);
})
.Select(x => x.month + "," + x.status + "," + x.late + "," + x.entry);
File.WriteAllLines(@"C:\temp\orders.csv", data);
From there, I've got something pretty close to what you've got (just without the year/day parts of the hierarchy:
At this point, I'm going to want to create a Measure, not a new column:
With the exact formula you used, I can create that measure and see our expected results:
The cool thing is, now you can use that measure against any logical dimension; you can get your measure by Year/Salesperson/Client etc.
Hopefully this helps.
Also, it's worth noting that you don't need the [Late] and [Entry] columns at all, if they don't have some other meaning. Your Measure's code could be something like this:
SomeMeasure = DIVIDE(COUNTX(Orders, Orders[DockDate] <= Orders[PromisedDate]), COUNTROWS(Orders))