Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
96 | |
80 | |
67 | |
62 |
User | Count |
---|---|
137 | |
106 | |
104 | |
81 | |
63 |