Forum Discussion
Michiel
10 years agoResolver III
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...
aalvear
10 years agoRegular Visitor
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
10 years agoRegular Visitor
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]