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
shelbsassy
Resolver I
Resolver I

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

 

WRONGWRONGRIGHTRIGHTWrong max service date per monthWrong max service date per monthmax service date per month - correctmax service date per month - correct

1 ACCEPTED SOLUTION

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!

View solution in original post

6 REPLIES 6
julian2064
New Member

.

v-shex-msft
Community Support
Community Support

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
If this post helps, please consider accept as solution to help other members find it more quickly.

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!

 

 

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
If this post helps, please consider accept as solution to help other members find it more quickly.

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!

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

Thank you!

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.