Difference while grouping

[GerardoRam]

So here's the thing, I'm creating a calculated table grouped by two other columms (Date and TruckID) from my original table. 

Auxiliar Camiones = CALCULATETABLE(GROUPBY(indice;Indice[TruckID];Indice[Date];"QTY";SUMX(CURRENTGROUP();Indice[QTY])))

Original (Indice):                                                                        NewTable (Auxiliar Camiones):

Date              |  TruckID      |  QTY    |   Line                                Date         | TruckID | QTY 

01/01/17       |  C140         | 200      |  1                                      01/01/17  | C140     | 310

01/01/17       |  C140         | 30        |  2                                      01/02/17  | C120     | 100

01/01/17       |  C140         | 80        |  3                                      01/03/17  | C140     | 180

01/02/17       |  C120         | 100      |  1                                      01/03/17  | C133     | 90

01/03/17       |  C140         | 100      |  1                                      

01/03/17       |  C140         |  80       |  2                                      

01/03/17       |  C133         |  90       |  1

From this new table I'm creating a measure: 

QTY_TRUCKS = DISTINCTCOUNT(TruckID)

Dates table new column from calculated table:

QTY_TRUCKS_DAY2 = [QTY_TRUCKS]

And after that I did a new column from this QTY_TRUCKS  in a related dates table in order to graph the average of trucks in each month.

Later I figured out that I could do the same thing but from the original table (creating a measure for the truck id and creating a new column in the related dates table).

Original table measure:

N°Trucks = DISTINCTCOUNT(Indice[TruckID])

Dates table new column from original table:

QTY_TRUCKS_DAY = [N°Trucks]

When comparing these two graphics I noticed that the one made from the calculated table has less trucks than the one from the original table. The question is , where could be the "error" that's generating those differences in number of trucks?

[MFelix]

Hi @GerardoRam,

One question just to understand all you set up why are you placing a new columnon your Dates table based on the measure you had? If you use the measure in your charts and the date table for axis this should work:

Measures are created to take into account the context of the visual it's in so no need to turn it into a column.

Also made the setup with the auxiliary table as you refer and the graphs is exaclty the same.

Regards,

MFelix

[GerardoRam]

I didso in order to make the monthly average of the number of trucks per day. I didn’t want to have the days in the X axis because the dataset is from a whole year and if I had the month name place in that axis, that measure would get me the number of different trucks used in the whole month.

I don’t really know where the difference could be, but when I looked at the tables (auxiliary and original), I had differences of 1-3 trucks for the same date (the auxiliary had less).
I’ll put some pictures later on today.

[v-ljerr-msft · Accepted solution]

Hi @GerardoRam,

I didso in order to make the monthly average of the number of trucks per day. I didn’t want to have the days in the X axis because the dataset is from a whole year and if I had the month name place in that axis, that measure would get me the number of different trucks used in the whole month.

---

In this scenario, you should be able to just create a single measure to sum the number of trucks each day. 

Measure = SUMX ( DateTable, DISTINCTCOUNT ( Indice[TruckID] ) )

Regards