Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Filter(all), static measures, and dynamic filter

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...
Need Help
parry2k's avatar
parry2k
Icon for Super User rankSuper User
3 years ago

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's avatar
    Anonymous
    Not applicable
    3 years ago

    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.