Filter(all), static measures, and dynamic filter

[Anonymous]

Hi, I have been playing around a small dataset (plz see attached) for a while, but still could not figure out a way to calculate the revenue projection # correctly. I think the problem comes from one of the embedded filters (Bill_status) in the measures. This built-in filter is also used as a page-level filter. 

• Revenue Projection (RP)= Distinct Patient Number (V1)* Average_Payment_Paid_Visit (V2)* Percentage_Paid_Patient(V3).
• The Distinct Patient Number (V1) for RP is based on 12 months.
• When V2 and V3 are calculated, the filters are: Fiscal Month = 11 months and Bill_Status= CLOSED.
• To calculate RP, Bill_Status should not be filtered (all choices of Bill_Status are selected).

The final output number is off.

The correct number for each clinic should be:

1. 01 -9,850,711
2. 02 -2,082,314
3. 03 -24,263,762
4. 04 -35,336,002

Here is the data modeling:

Here is the link to the PBIX file and test data file: https://drive.google.com/drive/folders/1AeaopCL63JdzBw0BkWkDbF0ShyNpZwwB?usp=sharing 

Thank you again for you help!

[parry2k]

@Anonymous there are too many measures in the question and business logic all over the place. To narrow down the issue, break down each measure and check which measure is not giving the correct result and then provide the details on that so that is a more focused approach.

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[Anonymous]

@parry2k Thanks for the suggestion!  I actually do not have an exact term to describe the problem.  I believe the issue stems from the revenue projection formula. I thnk the problem boils down to: 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 problem boils down to: 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?  Thank you.