Forum Discussion

Michiel's avatar
Michiel
Resolver III
10 years ago

Wrong total in table object

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...
Michiel's avatar
Michiel
Resolver III
10 years ago

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's avatar
austinsense
Impactful Individual
10 years ago

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's avatar
    aalvear
    Regular Visitor
    10 years ago

    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's avatar
      aalvear
      Regular Visitor
      10 years ago

      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]