Calculated column incorrect calculation

[KyleBlevins]

Hello,

I am extremely new to this software. So, please be gentle. I am trying to configure a dashboard for On-Time Delivery. Unfortunately, I have come across a road block. 

In the Image below, you can see the data from adding in my excel spreadsheet. It is grouped together with all the orders entered as Entry. So, January we had 39 orders shipped. Late signifies 2 being late. So, my formula is as follows. 

DIVIDE((SUM(Table13[Entry]) - SUM(Table13[Late])), SUM('Table13'[Entry]))

It should be (39-2)/39 = 94.87%

Figure 1 Excel Pivot Table Calculated OTD (Correct)Figure 2 with Power BI Calculated Column

[Anonymous · Accepted solution]

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.

[lovermann]

Add a new column and format it to shor percents...

[Anonymous]

I'm fairly certain you're looking for a measure here (not a calculated column), because you're wanting to see data not row by row, but rather across another aggregate (count of entry/late by month).

What does your underlying data look like? Are the Entry/Late columns being aggregated by sum or count? Is the [Column] column being aggregated by summing up many rows that are between 0 and 1?

This might help: Your [Column] column is actually showing numbers like 3145% (three thousand, one hundred and forty five percent).

Try isolating the things you've got in your formula. Make a column that's simply "TestColumn = SUM(Table13[Entry])" and see where that gets you. Try putting the same thing into a Measure.

EDIT: If your data is really row-by-row, then this is probably what you're trying to do:

[KyleBlevins]

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.

[KyleBlevins · Accepted solution]

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.

[Anonymous · Accepted solution]

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.

[Anonymous]

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))

[dtartaglia]

I'm not sure if this is what you really need but if I use your original example, put it in a spreadsheet then use this calc column:

Column = (Sheet1[Entry] - Sheet1[Late]) / Sheet1[Entry]

I get: