Percent of total per month

[amatulis]

Hi - I am trying to collect the percentage of total revenue a site contributes so that I can then use that value as a weighted percentage to attribute overhead to.

I can easily change the visual to give me percentage of grand totals but I am not sure how to make this a measure, and a measure that will calculate on an individual month basis so I can add up accurate year to date totals. 

Sample data where I just changed the revenue measure to display % of Grand Total. 

So Site A is going to be 11.28% * [Overhead], etc. for January but 12.55% * [Overhead] for March 

In the end I will be adding this Overhead shared cost to employee cost, then taking it against revenue for a margin. 

Margin = Revenue - EE Cost - Overhead Split 

[amatulis]

Great - thanks everyone.  I can see my first problem, and that makes sense.  I have a Company table that has the Site included but not a dedicated Site table.  I will add this today and do this formula and report back. 

[v-xicai]

Hi @amatulis ,

You may create measure like DAX below.

% of Grand Total= SUM(Table1[Revenue])/ CALCULATE(SUM(Table1[Revenue]), ALLEXCEPT(Table1, Table1[Site]))

Best Regards,

Amy 

Community Support Team _ Amy

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

[AllisonKennedy]

@amatulis  Yes, agree with what the others have posted. To put in more in terms of your data, you would want to try a MEASURE like: 

Revenue %perSite = DIVIDE(SUM(Table[Revenue]), CALCULATE(SUM(Table[Revenue]), ALL(Table[Sites]))

This will ensure that each site will have a different percentage, but that the grand total for Months will be 100% always.

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[amatulis]

I don't know if it's appropriate to comment on a thread I already closed or create new, but I'll start here and create new as needed.

I realized I had sites on the brain when I needed to actually attribtue overhead to the clients - no problem, just replaced the All(sites) with All(Clients) - happy to learn a new trick! 

The problem I am running into is that I guess I don't understand the underneath logic.  It works great if the table is just Company, but people will want to know which office they belong to.  So I added the Location (site) to the table, and the values all change and are not accurate at all.  

(And thus, trying to attribute monthly Overhead didn't work.  Although I'm sure I'll be back with questions about that process too)

Formula I used as a reference point:

Revenue %perSite = DIVIDE(SUM(Table[Revenue]), CALCULATE(SUM(Table[Revenue]), ALL(Table[Sites]))

Mine:

Revenue % Per Company = Divide([Revenue], CALCULATE([Revenue], ALL((Company[Company]))))

My Revenue comes from an invoices table that has monthly amounts, and connects to 2 lookup tables: calendar (to date of invoice) and company (and now company rolls up to location). 

[AllisonKennedy]

@amatulis I highly recommend you study up on the behaviour of ALL and other DAX filter functions if you want to truly understand and perfect this calculation. 

Without knowing your exact requirements, I can't say for certain, but if you are wanting to filter by company and location you could try: 

Revenue % Per Company and Location = Divide([Revenue], CALCULATE([Revenue], ALL(Company[Company], ALL(Location[Location])))

OR, if you are wanting only months to be filtered, you could try something like: 

Monthly Revenue % = DIVIDE([Revenue], CALCULATE([Revenue], ALLEXCEPT(Sales, Date[Month]))

Do read up a bit on ALL and ALLEXCEPT, and also note that if you want to use these to calculate other measures they will take on the context within which they are calculated. 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[amatulis]

Thanks for the extra detail Allison, this worked perfectly and exposed my main issue of not having a dedicated site table.

[sanimesa]

@amatulis  A measure like this will work for you:

% categoy sales = DIVIDE(SUM(Sales[Amount]), CALCULATE(SUM(Sales[Amount]), ALL('Product')))

This will work in each cell of your table as displayed. 

[Greg_Deckler]

I have this in my book. 

% of totals are basically:

PercentageGT = 
  DIVIDE(
    SUM('Table'[Value]),
    SUMX(ALL('Table'[Value])
  )

PercentageCT = 
  DIVIDE(
    SUM('Table'[Value]),
    SUMX(
      CALCULATETABLE(
        'Table',
        ALL('Table'[RowCategory])
      ),
      [Value]
    )

PercentageRT
  DIVIDE(
    SUM('Table'[Value]),
    SUMX(
      CALCULATETABLE(
        'Table',
        ALL('Table'[ColumnCategory])
      ),
      [Value]
    )