DAX Measure - Percent of Total Revenue = Multiple Years

[jcarrier]

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!

[tringuyenminh92]

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.

[jcarrier]

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 - Slicer

Pct of Total Revenue - Multiple Yr - Slicer

[tringuyenminh92]

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.

[jcarrier]

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

[tringuyenminh92]

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])))

[jcarrier]

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.

[tringuyenminh92]

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.

[jcarrier]

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

[jcarrier · 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)

[prakritnepal]

This was extremely helpful. Thanks