cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shelbsassy Member
Member

Percent column calculation not working as expected

I am trying to replicate a dashboard from scratch by copying the formulas and so far I have 2 issues where the formula is not working.  I posted what I thought would be a simple one here but it is baffling me: 

 

For this one I am trying to calculate the percentage of the max day of data divided by the total number of days in the month to give me the percentage.  In my old dashboard it is working as expected (screenshot) but copying the same formula yields different results.  Basically I need to get the max service date of claims that is loaded into the dataset so I can calculate the % trended per member per month (which is calculated at a point in time)  For example, For months 1-9 the maxpermonth is the last day of the month because the month is over.  But in this example theres only data as of Oct 25 so the maxpermonth for Oct is 25.  Therefore the calculation should be 31/25 = 1.25.

 

In my new dashboard I have the same calculation but for the rows in all the months it is showing 11/1 as the maxpermonth which is the max service date in the entire dataset, it is not showing the max date for each month like the other dashboard.

 

Thanks for any help!

 

 (Working Column) PercentColumn = CALCULATE(SUM(TrendCalcs[MonthDays])/sum(TrendCalcs[DataDay]),ALLEXCEPT(TrendCalcs,TrendCalcs[FirstDateC]))

 

DataDay = day(TrendCalcs[MaxPerMonth])  

 

Not Working

PercentColumn = CALCULATE(SUM(TrendCalcs[MonthDays])/sum('2017dates'[DataDay]),ALLEXCEPT(TrendCalcs,TrendCalcs[FirstDateC]))

 

wrong.pngWRONGright.pngRIGHTdataday.pngWrong max service date per month25.pngmax service date per month - correct

1 ACCEPTED SOLUTION

Accepted Solutions
shelbsassy Member
Member

Re: Percent column calculation not working as expected

I was able to create a measure using

Percent Measure Trend = DIVIDE(SUM('Trend'[MonthDays]),SUM('Trend'[Data Day]),0)

Then I got the amount:

Trended PMPM = ROUND([Percent Measure Trend] * [Actual PMPM],2)

 

Thanks for all the help!

6 REPLIES 6
Community Support Team
Community Support Team

Re: Percent column calculation not working as expected

Hi @shelbsassy,

 

You can try to use below formula to get correct percent.

 

MonthDays = DAY(DATE([StartDate].[Year],[StartDate].[MonthNo]+1,1)-1)
Percent = DIVIDE(DAY([EndDate]),[MonthDays],0) 

3.PNG4.PNG

 

Regards,

Xiaoxin Sheng

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



For learning resources/Release notes, please visit: | |
shelbsassy Member
Member

Re: Percent column calculation not working as expected

Thanks for your response.  I got the percent column working fine but I need to multiply it by the Actual PMPM (which is a measure) 

 

Actual PMPM = 'Members_Claims'[Total Paid]/'Members_Claims'[# Members]

 

I tried doing this measure:

 Trended PMPM = ROUND((CALCULATE('Members_Claims'[Actual PMPM]) * [Percent](FILTER(TrendCalcs,[FirstDate]))),2) 

which in the example should be $145.09 * 3.75 (data day is 8 so 30/8 = 3.75).  However I am getting $165.49 (with a percent of 1.14.)  I figured out what its doing is adding percent for months 1-12 (1,1,1,1,1,1,1,1,1,1,3.75,3.85) but then averaging months 1-11.  I only want the 3.75 row for November.  The correct result should be $145.09 * 3.75 = $559.08 for the trended pmpm.

 

My Percent Measure is : 

 

Percent = CALCULATE(DIVIDE(SUM('Trend'[MonthDays]),SUM('Trend'[Data Day])),FILTER(TrendCalcs_New,TrendCalcs_New[FirstDateC]))

 

Apparently something is going on with my filters but I am not very good at filtering in dax.

 

Do you have any suggestions on how I can get the calculation to be $559.08?

Thank you for your help!

 

 

shelbsassy Member
Member

Re: Percent column calculation not working as expected

Sorry, forgot to tag you @v-shex-msft

Thank you!

Community Support Team
Community Support Team

Re: Percent column calculation not working as expected

Hi @shelbsassy,

 

For your scenario, I think it may due to the conflict of internal filter and the current filter in the formula.

Can you please share the sample pbix file for test? 

 

Regards,

Xiaoxin Sheng

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



For learning resources/Release notes, please visit: | |
shelbsassy Member
Member

Re: Percent column calculation not working as expected

I was able to create a measure using

Percent Measure Trend = DIVIDE(SUM('Trend'[MonthDays]),SUM('Trend'[Data Day]),0)

Then I got the amount:

Trended PMPM = ROUND([Percent Measure Trend] * [Actual PMPM],2)

 

Thanks for all the help!

julian2064 New Member
New Member

Re: Percent column calculation not working as expected

.