cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ramz45 Regular Visitor
Regular Visitor

Help with DAX measure for cumulative percentage calculation

Hello, I have a table shown below.The positive and negative adjustment values are percentages - ex - 4% , 5% etc.

I am trying to write a measure that takes a number (output of another table) and calculate the amount it increase or decreases based on the percentages.

Exaxmple - Say the number I am feeding is 10. The output of year 1 is 10 + 10*4/100 = 10.4.

The output of year 2 is 10.4 +10.4*5/100. And so on.  

I am unable to create a calculated column as the input number is not fixed. Can someone kindly help with drafting a measure for this?

Capture.PNG

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
BILASolution Established Member
Established Member

Re: Help with DAX measure for cumulative percentage calculation

Hi @ramz45

 

Try this...

 

Feeding = 10
Acummulative Percentage = 

var actualyear = FIRSTNONBLANK(Adjustment[Year];1)

return

[Feeding]*
CALCULATE
(
	PRODUCTX
	(
		ADDCOLUMNS(Adjustment;"Adjustmentt";1 + DIVIDE(Adjustment[Positive Adjustment];100));
		[Adjustmentt]
	) ; ALL(Adjustment);Adjustment[Year] <= actualyear
)

And the final result is...

 

r.png

 

 

I hope this helps

 

Regards

BILASolution

7 REPLIES 7
Super User
Super User

Re: Help with DAX measure for cumulative percentage calculation

Hi,

 

  1. Share the data layout of the other table
  2. For the first year, why are you only using the numbers from the positive adjustment column.  Why not from the negative adjustment column?
  3. Please show your expected result.
BILASolution Established Member
Established Member

Re: Help with DAX measure for cumulative percentage calculation

Hi @ramz45

 

Try this...

 

Feeding = 10
Acummulative Percentage = 

var actualyear = FIRSTNONBLANK(Adjustment[Year];1)

return

[Feeding]*
CALCULATE
(
	PRODUCTX
	(
		ADDCOLUMNS(Adjustment;"Adjustmentt";1 + DIVIDE(Adjustment[Positive Adjustment];100));
		[Adjustmentt]
	) ; ALL(Adjustment);Adjustment[Year] <= actualyear
)

And the final result is...

 

r.png

 

 

I hope this helps

 

Regards

BILASolution

Highlighted
austinsense Established Member
Established Member

Re: Help with DAX measure for cumulative percentage calculation

Give This a Try ... What you need is the cumulative product of the "Positive Adjustment" column which requires a combination of PRODUCTX with a Dynamic Filter for getting the right dates.

 

NumberTimesDynamicCumulativePercent =

// Figure out the Maximum Year dynamically, this is our anchor for running a cumulative percent.

VAR MaxYear = MAX(Table[Year])

// Find the relevant cumulative years based on the anchor. We get the relevant rows, add one to the %, multiply them together to get our cumulative %. The first argument to the FILTER function where I wrote table, may need to be ALL(Table). Dropping the variable in there may mean we can get away with just Table.

VAR CumPerc = PRODUCTX(
                           FILTER(Table, Year <= MaxYear),
                           1 + Table[Positive Adjustment]) 

// This is the measure from your other table
VAR Number = SUM(OtherTable[Number])

RETURN

// now we're just multiplying our two variables together
Number * CumPerc
Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast Smiley Happy
ramz45 Regular Visitor
Regular Visitor

Re: Help with DAX measure for cumulative percentage calculation

Thank you Sir. I will try this out now

ramz45 Regular Visitor
Regular Visitor

Re: Help with DAX measure for cumulative percentage calculation

Thank you sir. I will try it out.

ramz45 Regular Visitor
Regular Visitor

Re: Help with DAX measure for cumulative percentage calculation

1. Hello, The data in the other table is about 10 million records so here is a sample. I have page filters that limit the data to 1 firstname, last name combo. I plan on using a SUMX measure in to calculate the total payment.

 

SUMX('CMS Data','CMS Data'[Medicare Payment]*'CMS Data'[Service Count])

 

I was planning on using this caluculation in the measure to calculate cumulative %.

 

Capture1.PNGCapture2.PNG

 

 

2. I will be using the values in the negative column as well. That will be a separate measure. Thought i could just replace the parameters from the measure for the positive column.

 

3. Sample output screenshot above

 

Thanks so much.

ramz45 Regular Visitor
Regular Visitor

Re: Help with DAX measure for cumulative percentage calculation

That seems to have done the trick. I am just running a couple more validations, but looks really promising. Thanks again.