(Editing) I believe the formula to calculate revenue projection is not correct. I need to have the Bill_Status =='CLOSED" static in calculating dyn_Avg_Payment_Paid_Visit and Dyn_Percent_Paid_Pad. In the meanwhile, I need the page-level filter - Bill_Status to be dynamic when I calculate revenue projection. Is there a way to tackle this?
I am breaking down the issue below.
Below is a list of the measures I developed (under the _Measures Table):
- Distinct_Pat_Num: measures a distinct count of patient visits based on patient visit ID with no filter criteria.
- CALCULATE (DISTINCTCOUNT(facttable, [PAT_VIT_ID]))
- Sta_Distinct_Pat_Num: measures a distinct count of patient number based on filter criteria: 11 months and Bill_status == “CLOSE.”
- CALCULATE(DISTINCTCOUNT(factable[PAT_VIT_ID]), FILTER(ALL(TimeTable), TimeTable[FiscalMonth]<>12), FILTER(ALL(BILL-DimensionTable), BILL[Bill_Status] ==’CLOSED’))
- Total_Payment: adding up all the payments.
- CALCULATE(SUMX(FACT TABLE’, ‘Fact Table’[TOT_PMTS]),filter(all(‘Fact Table’[TOT_PMTS]),‘Fact Table’[TOT_PMTS]<>0))
- Valid_Paid_Visit: measures payments (>0) resulting from valid patient visits.
- calculate([Distinct_Pat_Num], FILTER(ALL(‘FactTable’[TOT_PMTS]),’FactTable’[TOT_PMTS]<>0.00), FILTER(ALL(TimeTable),TimeTable[FiscalMonth]<>12),FILTER(ALL(FactTable’[BILL_STATUS]),’Fact Table’[BILL_STATUS]==“CLOSED”))
- Dyn_Avg_Payment_Paid_Visit: measures the average payment per patient visit for the past 11 months (excluding the fiscal month= 12)
- CALCULATE(DIVIDE([Total_Payments],[Valid_Paid_Pat_Visit]), FILTER(ALL(TimeTable),TimeTable[FiscalMonth]<>12),FILTER(ALL(Fact Table’[BILL_STATUS]), ‘Fact Table’[BILL_STATUS]==“CLOSED”))
- Dyn_Percent_Paid_Pat: measures the percentage of valid patient visit divided by the total distinct number of patient visits numbers for the past 11 months (excluding the fiscal month =12)
- CALCULATE(DIVIDE([Valid_Paid_Pat_Visit],[Sta_Distinct_Pat_Num]), FILTER(ALL(TimeTable),TimeTable[FiscalMonth]<>12),FILTER(ALL(‘Fact Table’[BILL_STATUS]),’Fact Table’[BILL_STATUS]==“CLOSED”))
The output numbers from above measures are all fine.
However, when I calculate the revenue projection, that’s when the problem comes up:
- Revenue Projection: I am using the 11 months of average payment of paid visit multiplied by the 11 months of percentage of paid patient visit, and then multiply the 12 months of distinct patient number to project the number for the next fiscal year.
- CALCULATE(SUMX(_Measures,[Distinct_Pat_Num][Dyn_Avg_Payment_Paid_Visit][Dyn_Percent_Paid_Pat]), ALLEXCEPT(BILL, BILL[BILL_STATUS]))
Both Dyn_Avg_Payment_Paid_Visit and Dyn_Percent_Paid_Pat need Bill_Status to be set as Closed.
However, in order to calculate the correct revenue projection number, I need to select all the options of Bill_Status. Right now, the page-level Bill_Status filter does not work.
The final output number is off.
The correct number for each clinic should be:
- 01 -9,850,711
- 02 -2,082,314
- 03 -24,263,762
- 04 -35,336,002
Here is the data modeling:
Here are the PBIX file and test data file.
Thank you again for you help!
OJ
PCPAYMENT_CSV914.pbix (9.5 MB)
testSample.csv (36.9 MB)