Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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 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
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
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.