Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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%?
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.
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
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |