Forum Discussion

quickbi's avatar
quickbi
Icon for Helper II rankHelper II
2 years ago

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 Amount = sum('PL Snapshot'[balance]) / -1000
Then, based on the account mapping, I generate a report using this formula:

 

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

  • Hi,

    The Infinity can be supressed by using the DIVIDE() function

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

    Hope this helps.

  • Anonymous's avatar
    Anonymous
    Not 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 He

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly