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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
brettq
Regular Visitor

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

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!

1 REPLY 1
v-lili6-msft
Community Support
Community Support

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

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.