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.
I have a calculated column that is taking the average of all data in the table but I just want to have the percentage by row. Basically I am trying to divide DataDay by MonthDays for each month to give me a percentage.
The percentage in the calculated column in giving me the aggregate of both Jan and Feb. I would like it to be Jan = 100% and Feb is 79% but it isgiving me .89 for both rows.
Any insights on how to use a filter? I have tried numerous options but nothing is working. Thank you!
Either - get rid of both SUM functions (you don't need to aggregate on each row)
PercentColumn = DIVIDE( TrendCalcs[DataDay] , TrendCalcs[MonthDays], 0 )
Or - wrap each SUM function in CALCULATE to make it respect the row context
PercentColumn 2 = DIVIDE ( CALCULATE ( SUM ( TrendCalcs[DataDay] ) ), CALCULATE ( SUM ( TrendCalcs[MonthDays] ) ), 0 )
Awesome thank you, I got that to work. However when I try to do the final calculation which is the Trended PMPM I am not getting that new column in my intellisense dropdown.
I need to calculate that [Percent Column] * [PMPM]
I have created both PMPM as a measure Members Claims[Total Paid]/Members Claims[# Members]
as well as a column PMPMC = 'Members Claims'[Total Paid]/'Members Claims'[# Members]
and I am trying to create Trended PMPM = [Percent Column]*[PMPM]
Can you see what I am doing wrong?
Thanks so much!
I suspect you don't like the results you are getting in the Total Row
Try these - all 3 are Measures
Percent Measure = DIVIDE ( SUM ( 'Members Claims'[DataDay] ), SUM ( 'Members Claims'[MonthDays] ), 0 ) PMPM Measure = DIVIDE ( SUM ( 'Members Claims'[TotalPaid] ), SUM ( 'Members Claims'[# Members] ), 0 ) Trended PMPM Measure = [Percent Measure] * [PMPM Measure]
Thank you for your reply.
I think I had my Percent Column backwards, but that is fixed. I tried what you suggested but the results do not change with the slicer and the numbers don't seem right.
For example, For Feb, I need to calculate 466.30 PMPM * 1.27 in the Percent Column to give me 592.20 for the Trended PMPM. I can't see to figure out how to get just that row for that month in the percent column.
Can you post some sample data?
I have scrambled the member information for protection and recreated a workbook to use as a sandbox.
For some reason I am having trouble recreating some of the formulas in my Dates table and TrendCalcs table. I am getting an error about circular dependency.
I am basically trying to get the latest date of the month the data was refreshed based on service date in order to calculate the trended PMPM.
What I am trying to accomplish is the trended or projected PMPM (per member per month) based on the claim activity received at that point in time in the month. I am trying to use the max service date for the month which for Jan is 1/31 and Feb is 2/22. The PMPM is Total Paid/# Members. To get the Trended PMPM it is total # days in month/max service date for month * PMPM.
Yesterday I worked on creating a percent column in the trend calc table that divides (for January) 31/31 for 1.00. So far we have 22 days of data in February so the calculation for Feb is 28/22= 1.27. So the Feb PMPM (cumulative) is $466.30 (Total Paid/# Members) but the trended should be 466.30 * 1.27 = 592.20 for the February trended PMPM.
I really appreciate any help you can offer. I have been struggling with this for a week now. Thank you so much!
Using the Percent Measure @Sean gave me yesterday as well as the Percent column, it is averaging in the measure but it is summing the column. I removed aggregation from the ones that I could and this is what it shows me. I am having a hard time filtering down to just that row of data so the PMPMP would be PMPM*1 for January and for February it would be PMPM *1.27.
This is my Percent Column calc
PercentColumn = CALCULATE(SUM(TrendCalcs[MonthDays])/sum(TrendCalcs[DataDay]),ALLEXCEPT(TrendCalcs,TrendCalcs[FirstDateC]))
I think I might have it. I have been scouring msdns Dax library and tried this:
Trended PMPM = CALCULATE('Members Claims'[PMPM]) * [Percent Measure](FILTER(TrendCalcs,[FirstDate]))
and I think it is doing what I want it to do. Fingers crossed!
Hi @shelbsassy
Please find attached the modified pbix file.
https://1drv.ms/u/s!ApP3mBZyGaHfzxy447kb0wpU5XJI
Check the calendar table.
And the change in the Trended table.
Cheers
CheenuSing
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 |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |