Percentage of Sales

[MarkusDW]

I am building a P&L model in PowerPivot. I have most of it working including granularity within the GL_Accounting structure and comparison to a yearly Budget. The only thing I cannot seem to figure out is how to create a column with the percentages of both revenue and expenses as a percentage of total sales. Ideally this would work with the same hierarchy of GL-Accounts as well as cumulative GL-Account.

It would be great if someone can help me out with this

[GilbertQ]

Hi @MarkusDW

This will give you the % of Grand Total

% of Grand Total = 
DIVIDE (
    [Measure Name],
    CALCULATE (
        [Measure Name],
        ALL ( 'Table Name' )
    )
)

And then what you can do is put in the Revenue or Expense and get the % Grand Total?

Hope that it helps?

Or are you looking to get the % of Revenue and % of Expenses and together they must add up to 100%?

[MarkusDW]

Thank you for helping out.

A percentage of Grand Total does not work because that includes all 3 GL groups (Sales, COGS and Expenses)

What I need is for every GL group in the P&L report to have the Amount as a percentage of Total Revenue. Also it needs to recognize the granular levels from the 3 main GL groups to Cummulative GL Accounts to Individual GL Accounts.

All data comes from Total_Transaction measure. I have a measure that finds the Total_Revenue but just dividing every total from Total_Transaction by Total_Revenue is not giving me the correct percentage.

[v-huizhn-msft]

Hi @MarkusDW,

If you create grand total for each groups, you'd better add ALLEXCEPT filter.

each group=CALCULATE(SUM(Table[value]),ALLEXCEPT(Table, Table[group]))

Total_Revenue is the sum of three groups' value? If it is, please use ALL function as formula above.

Total_Revenue=CALCULATE(SUM(Table[value]),ALL(Table))

Then create percentage. 

percentage=Table[each group]/Table[Total_Revenue]

If this is not want you want, would you mind share your data and expected result for further analysis?

Best Regards,
Angelia