Mostrando entradas con la etiqueta programación. Mostrar todas las entradas
Mostrando entradas con la etiqueta programación. Mostrar todas las entradas

Curso: calcular porcentajes

Para un % sobre un total de totales, hay que usar siempre SUMX.
El denominador puede ser global o parcial. 

Se aplicará global con ALL, donde esta función lo que hace es eliminar los filtros de página si los hubiera:

TotalVenta = SUMX(ALL('Ventas'), 'Ventas'[Ventas])

Por lo que para hacer el %, haremos lo siguiente:

%Venta =
    VAR PorcentVenta = SUM('Venta'[Venta])
    VAR TotalVenta = SUMX(all('Ventas'), 'Ventas'[Ventas])
RETURN
    DIVIDE(PorcentVenta, TotalVenta,0)

Pero lo bueno, es que no hace falta aplicar medidas. Tan sólo hay que poner el DATO en compilar un objeto visual, Valores, y hacer click en botón derecho. Luego, ir a 
























En el vídeo se explica en detalle:

Operador CALENDAR

 DimDate = VAR BaseCalendar =

    CALENDAR ( DATE (2019, 1, 1), NOW() )
RETURN
 GENERATE(
     BaseCalendar,
     VAR BaseDate = [Date]
     RETURN ROW (
         "DateKey", FORMAT (BaseDate, "yyyymmdd"),
         "Year", YEAR(BaseDate),
         "MonthName", FORMAT(BaseDate, "mmmm"),
         "MonthNumber", FORMAT(BaseDate, "m"),
         "Quarter", "Qtr " & FORMAT(BaseDate, "q")
     )
 )

AVERAGE: Calcular el coste medio de las mercancías vendidas x día:

 Calcular el coste medio de las mercancías vendidas x día:


Total coste mercancias vendidas = AVERAGEX(VALUES('FECHAS'[Date]),
    (CALCULATE(
    SUM('Registro de Entradas y Salidas'[Costos de salida]),
    'Registro de Entradas y Salidas'[Desc. tipo]="Factura")
    )
)

Operador Switch

principales funciones de SWITCH:

COMPARAR y crear una columna nueva

 = SWITCH (

[Month Number Of Year], 1, "January", 2, "February", 3, "March", 4, "April", 5, "May", 6, "June", 7, "July", 8, "August", 9, "September", 10, "October", 11, "November", 12, "December", "Unknown month number" )

COMPARAR IF, ELSE anidadas

= SWITCH ( TRUE, [Reorder Point] > [Safety Stock Level], "Good: Safety stock level exceeded", [Reorder Point] = [Safety Stock Level], "Minimal: Safety stock level met", [Reorder Point] < [Safety Stock Level], "At risk: Safety stock level not met", ISBLANK ( [Reorder Point] ), "Incomplete: Reorder point not set", ISBLANK ( [Safety Stock Level] ), "Incomplete: Safety stock level not set", "Unknown" )



Ejemplo VARIABLE

% personas aplican =
VAR __ppaplican=
CALCULATE(
[Total encuestados],
RRHH[Postulación]="Aplica"
)
return
DIVIDE(__ppaplican, [Total encuestados])

Conversion Grades

Total Letter Grade =
SWITCH (
    TRUE (),
    [Total Grade] >= 1.0, "A+",
    [Total Grade] < 1.00
        && [Total Grade] >= 0.925, "A",
    [Total Grade] < 0.925
        && [Total Grade] >= 0.895, "A-",
    [Total Grade] < 0.895
        && [Total Grade]  >= 0.875, "B-",
    [Total Grade] < 0.875
        && [Total Grade] >= 0.825,  "B",
    [Total Grade] < 0.825
        && [Total Grade] >= 0.795,  "B-",
    [Total Grade]  < 0.795
        && [Total Grade] >= 0.775, "C+",
    [Total Grade] < 0.775
        && [Total Grade] >= 0.725,  "C",
    [Total Grade] < 0.725
        && [Total Grade] >= 0.695, "C-",
    [Total Grade] < 0.695
        && [Total Grade] >= 0.675, "D+",
    [Total Grade] < 0.675
        && [Total Grade] >= 0.625, "D",
    [Total Grade] < 0.625
        && [Total Grade] >= 0.600, "D-",
    "F"
)

Conversión fechas

(StartDate as date, EndDate as date)=>

let
    //Capture the date range from the parameters
    StartDate = #date(Date.Year(StartDate), Date.Month(StartDate), 
    Date.Day(StartDate)),
    EndDate = #date(Date.Year(EndDate), Date.Month(EndDate), 
    Date.Day(EndDate)),

    //Get the number of dates that will be required for the table
    GetDateCount = Duration.Days(EndDate - StartDate),

    //Take the count of dates and turn it into a list of dates
    GetDateList = List.Dates(StartDate, GetDateCount, 
    #duration(1,0,0,0)),

    //Convert the list into a table
    DateListToTable = Table.FromList(GetDateList, 
    Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),

    //Create various date attributes from the date column
    //Add Year Column
    YearNumber = Table.AddColumn(DateListToTable, "Year", 
    each Date.Year([Date])),

    //Add Quarter Column
    QuarterNumber = Table.AddColumn(YearNumber , "Quarter", 
    each "Q" & Number.ToText(Date.QuarterOfYear([Date]))),

    //Add Week Number Column
    WeekNumber= Table.AddColumn(QuarterNumber , "Week Number", 
    each Date.WeekOfYear([Date])),

    //Add Month Number Column
    MonthNumber = Table.AddColumn(WeekNumber, "Month Number", 
    each Date.Month([Date])),

    //Add Month Name Column
    MonthName = Table.AddColumn(MonthNumber , "Month", 
    each Date.ToText([Date],"MMMM")),

    //Add Day of Week Column
    DayOfWeek = Table.AddColumn(MonthName , "Day of Week", 
    each Date.ToText([Date],"dddd"))

in
    DayOfWeek