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...
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]