Wrong total in table object

[Michiel]

I'm working on a report on billable hours for employees for a company. The billing ratio is defined as total billable hours divided by total workable hours (both from the same fact table, billable is a subcategory of all hours).

The company is organized in departments, and I have a bar chart with the billing ratio for each department. On the same view, I have a table with employees and their individual billing ratio. The idea is that when the user clicks a department bar, the table is filtered to the employees in that department.

Here's the problem: the billing ratio is shown correctly in the bar chart, but when selecting a department, the total billing ratio in the table (on the total row) has a different value. I've tried the same thing in Excel (without the interactivity of course) and there the correct result is shown.

What could be wrong here?

[aalvear]

I´m having the same issue see the total row

Help  Please

[Michiel]

Thanks for your replies. After some investigating, I find the problem is not in a filter that I have, but in the behaviour of the table object. It happens that there are employees that don't show up in the table, because they don't have results for the columns I have in the table (like no billable hours and >0 workable hours, resulting in a blank value for billing ratio).

The issue is that apparently, the table computes the total line over only the lines that are visible in the table, not over all underlying data rows. This returns an incorrect total value (employees who have no billable hours but did have workable hours, should count for the overall billing ratio as well).

Is this a bug or by design?

The workaround, obviously, is to make sure there's always a column in the table object that contains values for all data rows.

[austinsense]

This is by design and should be dealt with in your measures. The table is interpreting, very literally, what you asked it to do in the measure.  Share your code in these situations so that people can help diagnose the issue.

[aalvear]

I  think these are de codes you are asking

This is for my measures:

ventas mtd = TOTALMTD(SUM(Ventas[Ventas]);Fecha[Fecha])
ant ventas mes = CALCULATE(Ventas[ventas mtd];SAMEPERIODLASTYEAR(Fecha[Fecha]))
var ventas mes = Ventas[ventas mtd]-Ventas[ant ventas mes]

I also change my  measure "ant ventas mes" to :

IF(Ventas[ventas mtd]=0;0 ;CALCULATE(Ventas[ventas mtd];SAMEPERIODLASTYEAR(Fecha[Fecha])))

This make the Total row = 0

This is the code I have for the Ventas Table:

let
    Origen = Excel.Workbook(File.Contents("C:\Users\Nicolita\Documents\PBI\PBI copiar\CursoPBI\sales.xlsx"), null, true),
    Tabla1_Table = Origen{[Item="Tabla1",Kind="Table"]}[Data],
    #"Tipo cambiado" = Table.TransformColumnTypes(Tabla1_Table,{{"ID Producto", Int64.Type}, {"Fecha", type date}, {"Zip", Int64.Type}, {"Unidades", Int64.Type}, {"Ventas", type number}}),
    #"Consulta anexada" = Table.Combine({#"Tipo cambiado", International}),
    #"Filas filtradas" = Table.SelectRows(#"Consulta anexada", each true),
    #"Personalizada agregada" = Table.AddColumn(#"Filas filtradas", "Custom", each if [Pais] = null then "USA" else [Pais]),
    #"Filas filtradas1" = Table.SelectRows(#"Personalizada agregada", each true),
    #"Columnas con nombre cambiado" = Table.RenameColumns(#"Filas filtradas1",{{"Custom", "PAISES"}}),
    #"Personalizada agregada1" = Table.AddColumn(#"Columnas con nombre cambiado", "Custom", each if [Pais] = null then "USA" else [Pais]),
    #"Columnas quitadas" = Table.RemoveColumns(#"Personalizada agregada1",{"Pais"}),
    #"Columnas con nombre cambiado1" = Table.RenameColumns(#"Columnas quitadas",{{"Custom", "Pais"}}),
    #"Columnas quitadas1" = Table.RemoveColumns(#"Columnas con nombre cambiado1",{"PAISES"})
in
    #"Columnas quitadas1"

[aalvear]

I all ready solved calculating the date in other way

 I use this:

Total Sales = SUM(Ventas[Ventas])

ventas mtd = CALCULATE([Total Sales];Fecha[Month])

ant ventas mes = IF(Ventas[ventas mtd]=0;0 ;CALCULATE(Ventas[ventas mtd];SAMEPERIODLASTYEAR(Fecha[Fecha])))

var ventas mes = [TOTAL Sales] -Ventas[ant ventas mes]

[Sebastian]

Hi Michiel,

I have the same problem. The matrix shows a total value of all lines that are in the table.

The sum of all visible lines in the matrix would be correctly.

I think taht this is a bug.

[Nhallquist]

I would also check your data.  I work for a similar company and where we run into problems with reporting like this when an associate bills time for a different office than his own.  For example.  We will have Associates in Office A, work on a project for Office B.  The Billable hours are recorded as going to Office B, even though the Associate works in Office A.  What happens then is our managers in Office A filter for Office A, and see "wrong" results.  However, if they select all the individuals from Office A, the numbers are correct.  This happens becuase the hours are associated by Person, and project, and not really by the office of the staff completing the hours.  

I hope this explanation helped.  

[Phil_Seamark]

Do you have any filters selected for your visual?  These could be at Visual, Page or Report level.  Try playing with these to see if they have an effect on the behaviour of the total.  This won't solve your issue but may help you understand where the problem may lie.