Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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!
.
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
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
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!
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |