Columna de porcentajes de error

[Syndicate_Admin]

Hola a todos

Estoy creando una tabla basada en @amitchandak ejemplo donde clasifico los gastos e ingresos de una empresa en base a un mapeo externo. La fórmula base es la siguiente:

PL Importe = sum('PL Snapshot'[saldo]) / -1000

A continuación, en función de la asignación de cuentas, genero un informe utilizando esta fórmula:

PL Subtotals = 
SWITCH(
    TRUE(),
    MAX('PL Account'[Order_id]) = 5,CALCULATE([PL Amount],FILTER(ALL('PL Account'),'PL Account'[Order_id] IN {1, 2,3,4})), -- as Gross Revenues
    MAX('PL Account'[Order_id]) = 7,(CALCULATE([PL Amount],FILTER(ALL('PL Account'),'PL Account'[Order_id] IN {1, 2,3,4})))+(CALCULATE([PL Amount],FILTER(ALL('PL Account'),'PL Account'[Order_id] IN {6}))), -- Net Revenues
    MAX('PL Account'[Order_id]) = 11,CALCULATE([PL Amount],FILTER(ALL('PL Account'),'PL Account'[Order_id] IN {8,9,10})), -- as Total Food Consumption
    MAX('PL Account'[Order_id]) = 15,CALCULATE([PL Amount],FILTER(ALL('PL Account'),'PL Account'[Order_id] IN {12,13,14})), -- as Total Beverage Consumption
    MAX('PL Account'[Order_id]) = 20,CALCULATE([PL Amount],FILTER(ALL('PL Account'),'PL Account'[Order_id] IN {16,17,18,19})),-- as total Packaging
    -- *****************COGS**************
    MAX('PL Account'[Order_id]) = 21,
    +CALCULATE([PL Amount],FILTER(ALL('PL Account'),'PL Account'[Order_id] IN {8,9,10})) -- as Total Food Consumption
    +CALCULATE([PL Amount],FILTER(ALL('PL Account'),'PL Account'[Order_id] IN {12,13,14})) -- as Total Beverage Consumption
    +CALCULATE([PL Amount],FILTER(ALL('PL Account'),'PL Account'[Order_id] IN {16,17,18,19})),-- as total Packaging
    -- *****************COGS**************
    MAX('PL Account'[Order_id]) = 22,
    CALCULATE([PL Amount],FILTER(ALL('PL Account'),'PL Account'[Order_id] IN {22})), -- as Rappels
       -- *****************Gross MArgin**************
    MAX('PL Account'[Order_id]) = 23,
    (CALCULATE([PL Amount],FILTER(ALL('PL Account'),'PL Account'[Order_id] IN {1, 2,3,4})))+(CALCULATE([PL Amount],FILTER(ALL('PL Account'),'PL Account'[Order_id] IN {6}))) -- Net Revenues
    +CALCULATE([PL Amount],FILTER(ALL('PL Account'),'PL Account'[Order_id] IN {8,9,10})) -- as Total Food Consumption
    +CALCULATE([PL Amount],FILTER(ALL('PL Account'),'PL Account'[Order_id] IN {12,13,14})) -- as Total Beverage Consumption
    +CALCULATE([PL Amount],FILTER(ALL('PL Account'),'PL Account'[Order_id] IN {16,17,18,19}))-- as total Packaging
    +CALCULATE([PL Amount],FILTER(ALL('PL Account'),'PL Account'[Order_id] IN {22})) -- as Rappels
    -- *****************Gross MArgin**************
    -- *****************Commision fees**************
    ,MAX('PL Account'[Order_id]) = 28,
    +CALCULATE([PL Amount],FILTER(ALL('PL Account'),'PL Account'[Order_id] IN {24,25,26,27}))-- as Commission Fees
    -- *****************Commision fees**************
    -- *****************Direct Personnel**************
    ,MAX('PL Account'[Order_id]) = 37,
    +CALCULATE([PL Amount],FILTER(ALL('PL Account'),'PL Account'[Order_id] IN {29,30,31,32,33,34,35,36}))-- as Direct Personnel
    -- *****************Direct Personnel**************
    -- *****************Marketing**************
    ,MAX('PL Account'[Order_id]) = 42,
    +CALCULATE([PL Amount],FILTER(ALL('PL Account'),'PL Account'[Order_id] IN {38,39,40,41}))-- as Marketing
    -- *****************Marketing**************
        -- *****************Occupancy**************
    ,MAX('PL Account'[Order_id]) = 45,
    +CALCULATE([PL Amount],FILTER(ALL('PL Account'),'PL Account'[Order_id] IN {43,44}))-- as Occupancy
    -- *****************Occupancy**************
        -- *****************Cleaning and edibles**************
    ,MAX('PL Account'[Order_id]) = 51,
    +CALCULATE([PL Amount],FILTER(ALL('PL Account'),'PL Account'[Order_id] IN {46,47,48,49,50}))-- as Cleaning and edibles
    -- *****************Cleaning and edibles**************
            -- *****************IPS**************
    ,MAX('PL Account'[Order_id]) = 56,
    +CALCULATE([PL Amount],FILTER(ALL('PL Account'),'PL Account'[Order_id] IN {52,53,54,55}))-- as IPS
            -- *****************IPS**************
            -- *****************Total Direct operating expenses**************
    ,MAX('PL Account'[Order_id]) = 58,
    +CALCULATE([PL Amount],FILTER(ALL('PL Account'),'PL Account'[Order_id] IN {46,47,48,49,50,51,55,56,57,58}))-- as IPS
            -- *****************Total Direct operating expenses**************
            -- *****************Brand and partnerships**************
    ,MAX('PL Account'[Order_id]) = 68,
    +CALCULATE([PL Amount],FILTER(ALL('PL Account'),'PL Account'[Order_id] IN {60,61,62,63,64,65,66,67,68}))-- as Brand and partnerships
            -- *****************Brand and partnerships**************            


    ,[PL Amount] 
)
​

El problema viene cuando quiero crear otra columna para crear el porcentaje de ventas netas.
Ventas netas:

Total Amount for Net Sales = 
CALCULATE(
    SUM('PL Snapshot'[balance])/-1000,
    Accounts[isnetsales] = 1
)
​

Y el cálculo que devuelve infinito:

% of Sales = [PL Subtotals]/[Total Amount for Net Sales]​

Resultado:

¿Alguna idea de cómo hacerlo? graciasssss

[Syndicate_Admin]

Hola @quickbi ,
Si el asesoramiento dado por @Ashish_Mathur ha resuelto su confusión, si el problema se ha resuelto, puede marcar la respuesta de la respuesta estándar para ayudar a los demás miembros a encontrarla más rápidamente. Si no es así, sírvase señalarlo.

Saludos
Alberto He

Si esta publicación ayuda, considere Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente

[Syndicate_Admin]

Hola

El Infinity se puede suprimir usando la función DIVIDE()

% of Sales = DIVIDE([PL Subtotals],[Total Amount for Net Sales]​)

Espero que esto ayude.