Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Syndicate_Admin
Administrator
Administrator

Columna de porcentajes de error

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:
quickbi_0-1717947814330.png


¿Alguna idea de cómo hacerlo? graciasssss


2 REPLIES 2
Syndicate_Admin
Administrator
Administrator

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
Administrator
Administrator

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.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors