Forum Discussion
Error making percentages column
Hi everyone
I am creating a table based on amitchandak example where I classify the expenses and income of a company based on an external mapping. The base formula is this:
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]
)
The problem comes when I want to create another column to create the percentage of net sales.
Net sales:
Total Amount for Net Sales =
CALCULATE(
SUM('PL Snapshot'[balance])/-1000,
Accounts[isnetsales] = 1
)
And the calculation that returns infinity:
% of Sales = [PL Subtotals]/[Total Amount for Net Sales]Result:
Any idea how to do it? thankssss
2 Replies
- Ashish_Mathur
Super User
Hi,
The Infinity can be supressed by using the DIVIDE() function
% of Sales = DIVIDE([PL Subtotals],[Total Amount for Net Sales])Hope this helps.
- AnonymousNot applicable
Hi quickbi ,
Whether the advice given by Ashish_Mathur has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.Best regards,
Albert HeIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly