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
jcarrier
Helper I
Helper I

DAX Measure - Percent of Total Revenue = Multiple Years

I have been working with DAX for quite some time now, and I've come across a rather complex Percent of Total Revenue request.  I'm thinking DAX should be able to accomplish this fairly easily as Pivot Table calculated fileds makes it easy enough.  First off, to calculate Percent of Total Revenue for a single year, I simply use the following formula:

 

DIVIDE ([Amount], CALCULATE(SUM([Amount]), 'Table'[Report Category]="Revenue"), 0)

 

Again, that works great for a single year of analysis, and I've also used the following to incorporate Slicers into the equation:

 

DIVIDE([Amount], CALCULATE(SUM([Amount]), FILTER(ALLSELECTED(''Table'), Table'[Report Category]="Revenue")), 0)

 

Again, that works great for selecting a single year at a time for the Pivot Tables developed from the Data Model.  The problem is that if I select multiple years SIMULTANEOUSLY, then the DAX calculation calculates the denominator as the total Revenue for all selected years rather than calculate Percent of Total Revenue utilizing the total Revenue in each given year as the denominator.

 

I'd greatly appreciate any insight/solution to providing the right DAX formula.  Thanks!

1 ACCEPTED SOLUTION

Solved; thanks for your help!  The key to the final solution proved to be that the DAX formula was mostly correct but the filtering just needed to reference the Dimension Table rathen than the Fact Table as previously calculated.  That allows the Percent of Total Revenue (or any specific line item for that matter) to properly filter the denominator of the calculation utilizing Slicers for multiple years simultaneously.

 

Calculated Field 1:=DIVIDE([SIMPLE BALANCE], CALCULATE(SUM([BALANCE]), FILTER(ALLSELECTED('DimensionTable'), ‘DimensionTable’[REPORT_CATEGORY]="Revenue")), 0)

View solution in original post

10 REPLIES 10
tringuyenminh92
Memorable Member
Memorable Member

Hi @jcarrier,

 

if you could share your current data model and sample data with expectation picture, I could figure out proper solution for your case cause so far I'm not sure I understand your case correctly.

I have provided two screenshot illustrating the problem in correctly calculating Percent of Total Revenue for Multiple Years simultaneously.  The first screenshot illustrates that it works correctly as a Percent of Total Revenue when a Single Year is selected at one time from the Year slicer.  The second screenshot illustrates that it does not work correctly as a Percent of Total Revenue when Multiple Years are selected at one time from the Year slicer.  The denominator of the calculation is based on total revenue for all years combined rather than properly establishing the denominator for each year reported within the Pivot Table report.  Again, here is the DAX measure utilized for the calculation.

 

Calculated Field 1:=DIVIDE([SIMPLE BALANCE], CALCULATE(SUM([BALANCE]), FILTER(ALLSELECTED('Table'), ‘Table’[REPORT_CATEGORY]="Revenue")), 0)

 

 

Pct of Total Revenue - Single Yr - SlicerPct of Total Revenue - Single Yr - Slicer

 

Pct of Total Revenue - Multiple Yr - SlicerPct of Total Revenue - Multiple Yr - Slicer

Hi @jcarrier,

 

Did you try with ALLSELECTED('Table'[Year])? I'm not sure, will try create sample data and re-produce your case with my suggestion. Hope it works.

No luck with that approach.  Pleae let me know if you can have any success.  Thanks.

Hi @jcarrier

 

I'm sorry, it should be ALLSELECTED(Data[REPORT_Line_Item])

Something like: 

% of column total = DIVIDE(SUM(Data[Balance]),CALCULATE(SUM(Data[Balance]),ALLSELECTED(Data[Line_Item])))

Screenshot 2017-08-10 22.27.29.png

I'm not getting the same result based on the DAX forumla provided.  If I use that formula, the denominator is simply lthe same as the numerator for every row (selecting either a single or multiple years).  I think the primary key is to solve the denominator calculation so that each year utilizes total revenue for either a single year or multiple years selected.  Please let me know if you have a solution.  Thanks again.

Hi @jcarrier,

 

Could I have your sample data, maybe 2 years data of 20010 and 20011 or older. If you dont to public 2 years data, you could drop inbox to me.

tringuyenminh92 -

 

I sent you a OneDrive link to the file the other day.  Did you recieve it, and have a chance to take a look?  Please let me know.  Thanks.

 

jcarrier

Solved; thanks for your help!  The key to the final solution proved to be that the DAX formula was mostly correct but the filtering just needed to reference the Dimension Table rathen than the Fact Table as previously calculated.  That allows the Percent of Total Revenue (or any specific line item for that matter) to properly filter the denominator of the calculation utilizing Slicers for multiple years simultaneously.

 

Calculated Field 1:=DIVIDE([SIMPLE BALANCE], CALCULATE(SUM([BALANCE]), FILTER(ALLSELECTED('DimensionTable'), ‘DimensionTable’[REPORT_CATEGORY]="Revenue")), 0)

This was extremely helpful. Thanks

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.