Latest Enterprise DNA Initiatives

Filter(all), static measures, and dynamic filter

(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.
  • 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:

  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 are the PBIX file and test data file.

Thank you again for you help!

PCPAYMENT_CSV914.pbix (9.5 MB)
testSample.csv (36.9 MB)

bumping this post for visibility

Hi @powerbiuser! We noticed that your inquiry was left unsolved for quite some time now.

Looks like your inquiry was out of the experts and users’ bounds.

We strive to answer ALL inquiries in the forum. However, if you are sure that you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, you can check out tutorials to learn this yourself as your membership also comes with relevant resources that may help you with your Power BI education.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!