- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Percent column calculation not working as expected

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

shelbsassy

Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-08-2017
08:06 AM

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

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

shelbsassy

Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-14-2017
05:01 AM

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

v-shex-msft

Community Support Team

Re: Percent column calculation not working as expected

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-09-2017
05:46 PM

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)

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

If this post

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

shelbsassy

Member

Re: Percent column calculation not working as expected

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-10-2017
08:08 AM

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

Re: Percent column calculation not working as expected

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-10-2017
08:09 AM

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

Thank you!

v-shex-msft

Community Support Team

Re: Percent column calculation not working as expected

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-13-2017
10:59 PM

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

If this post

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

shelbsassy

Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-14-2017
05:01 AM

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

Re: Percent column calculation not working as expected

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-01-2018
02:30 PM

.