Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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...
I hope this helps
Regards
BILASolution
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
Thank you sir. I will try it out.
Thank you Sir. I will try this out now
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...
I hope this helps
Regards
BILASolution
That seems to have done the trick. I am just running a couple more validations, but looks really promising. Thanks again.
Hi,
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 %.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |