Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MarkusDW
New Member

Percentage of Sales

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

3 REPLIES 3
GilbertQ
Super User
Super User

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%?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.