Comparing "Percent of Total" to "Maximum Percent of Total" across different groups

[brettq]

Hi all,

I have a requirement to show sales people the percentage of their total revenue by year that a specific product group represents. Fairly simple.

Now, I need to compare the above percentage to the maximum percentages for the same product across all sales territories. I'm stuck.

Example:

Product Grouping A comprises 50% of total revenue in 2017 for Territory X.

Product Grouping A comprises 70% of total revenue in 2017 for Territory Y (This is the greatest percentage).

When the report is filtered to Territory X, I need to show the 50% compared to the 70% for Product Grouping A... in addition to the relevant percentages for Groupings B, C, etc...

Standard sales transaction fact table:

Sales *-1 Fiscal Calendar

Sales *-1 Products

Sales *-1 Customers

Customers *-1 Sales Territories

What I have so far:

Revenue = SUM(Sales[Total Price])

% of Total Revenue = 
VAR TotalRev = SUMX(ALL(Products[Mapping]), [Revenue])
RETURN
DIVIDE([Revenue],TotalRev)

Now I am trying to calculate the "Maximum percent of total revenue across all territories for given product."

Measure =
VAR GroupRevenue = CALCULATE([Revenue], ALLEXCEPT(Sales, 'Calendar'[Fiscal Year], Org[Territory], Products[Group]))
VAR TotalRevenue = CALCULATE([Revenue], ALLEXCEPT(Sales, 'Calendar'[Fiscal Year], Org[Territory]))
VAR CalcTable = SUMMARIZE(Sales, 'Calendar'[Fiscal Year], Products[Mapping], Org[Territory], "Percent of Whole",MappingRevenue / TotalRevenue)
RETURN
MAXX(CalcTable, [Percent of Whole])

However, it appears the "CalcTable" still holds the Territory row context. I've tried using ALL, ALLEXCEPT, CALCULATETABLE all over. I may be overthinking this and need to take a step back. Would appreciate any advice. Thanks!

[v-lili6-msft]

hi,@brettq

     You can try to use this formula like below:

Measure = MAXX(ALLEXCEPT(Sales,Sales[Products]),[% of Total Revenue])

If not your case, Please share some data sample and the expected output or demo pbix. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.
 

Best Regards,

Lin