Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I am new to Power BI and have spent days trying to get a measure to work with no luck. I have spent hours reading blogs and trying everything under the sun to get this to work. I have a single flat file data source with hospital revenue data so I do not have a complex model. I need to calculate counts of FINs (patients) that have have a bed charge without a physician charge. In the end, I need a total Count of FINs (with bed charge without physician charge) sliced location etc. I have this working except there is no total at the bottom of the table visualization...so any slicing of this measure by anything other than FIN yields blanks (IE slice by location). I have a measure that didn't sum 100% correct in the table visualization but did produce a grand total in the table visualization. After researching I used SUMX and it sums 100% correctly now but does NOT yield a grand total in the table visualization. I would appreciate any help as I am totally stuck.
These supporting measures were tested and work:
Quantity_ADP = CALCULATE(SUM(Revenue_Extract[Total_Quantity]),Revenue_Extract[Activity_Type] = "Admit/Transfer/Discharge")
Quantity_PersonManagement = CALCULATE(SUM(Revenue_Extract[Total_Quantity]),Revenue_Extract[Activity_Type] = "Person Management")
Quantity_PhysicianCharges = CALCULATE(SUM(Revenue_Extract[Total_Quantity]),Revenue_Extract[Activity_Type] = "Physician Charges")
This doesn't sum correctly when the above quantites roll up; however, it has a grand total and yields a number when sliced by attributes other than FIN:
FIN_Count_NoPhysicianCharges_OLD = CALCULATE(DISTINCTCOUNT(Revenue_Extract[FIN]), FILTER(Revenue_Extract, ([Quantity_PhysicianCharges] <= 0 && ([Quantity_ADP] +[Quantity_PersonManagement]) > 0 && SUM(Revenue_Extract[Total_Charge_Amount]) > 0)))
This sums perfectly when sliced by FIN except there is no total on the table so slicing with anything other than FIN doesn't work:
FIN_Count_NoPhysicianCharge = CALCULATE(DISTINCTCOUNT(Revenue_Extract[FIN]), FILTER(Revenue_Extract, (SUMX(Revenue_Extract,[Quantity_PhysicianCharges]) <= 0 && (SUMX(Revenue_Extract,([Quantity_ADP]+[Quantity_PersonManagement])) > 0 && SUM(Revenue_Extract[Total_Charge_Amount]) > 0))))
I couldn't include a snip of the table visualization but this is what the result basically looks (below) like for a single FIN. Everything is corerct except FIN_Count_NoPhysicianCharge has no total.
FIN | Quantity_ADP | Quantity_PersonManagement | Quantity_PhysicianCharges | Total_Charge_Amount | NoPhysicianCharge_COL | FIN_Count_NoPhysicianCharge | FIN_Count_NoPhysicianCharges_OLD |
xxxxx | -1 | 2 | -277 | FALSE | |||
xxxxx | 6 | 5460 | TRUE | 1 | 1 | ||
total | 5 | 2 | 5183 | 1 |
Solved! Go to Solution.
6 days later I figurerd this out myself. The original measure needed to be modified to this:
FIN_Count_NoPhysicianCharge_OLD = (CALCULATE(DISTINCTCOUNT(Revenue_Extract[FIN]), FILTER(values(Revenue_Extract[FIN]), ([Quantity_PhysicianCharges] <= 0 && ([Quantity_ADP] +[Quantity_PersonManagement]) > 0 && SUM(Revenue_Extract[Total_Charge_Amount]) > 0))))
Hi,
Share the link from where i can download your file. Please show the exact table where you are facing the problem and also your expected solution.
here is a slimmed down data source:
Activity_Date | FIN | Total_Charge_Amount | Total_Quantity | Activity_Type | Patient_Facility |
2/7/2018 | 504 | 910 | 1 | Admit/Transfer/Discharge | BrownDeer |
2/9/2018 | 504 | 326 | 1 | Physician Charges | BrownDeer |
2/9/2018 | 504 | 910 | 1 | Admit/Transfer/Discharge | BrownDeer |
2/8/2018 | 504 | 910 | 1 | Admit/Transfer/Discharge | BrownDeer |
2/7/2018 | 503 | 910 | 1 | Admit/Transfer/Discharge | Tampa |
2/6/2018 | 503 | 910 | 1 | Admit/Transfer/Discharge | Tampa |
2/8/2018 | 503 | 910 | 1 | Admit/Transfer/Discharge | Tampa |
2/9/2018 | 503 | 910 | 1 | Admit/Transfer/Discharge | Tampa |
Here are expected results (mocked up table visualization):
FIN | Quantity_ADP | Quantity_PersonManagement | Quantity_PhysicianCharges | Total_Charge_Amount | FIN_Count_NoPhysicianCharge_OLD | FIN_Count_NoPhysicianCharge |
503 | 4 | 3640 | 1 | 1 | ||
504 | 3 | 1 | 3056 | 1 (should be blank) | ||
TOTAL | 7 | 1 | 6696 | 2 (should be 1) | (should be 1 but its blank) |
FIN_Count_NoPhysicianCharge_OLD is incorrectly calulating a 1 count for 504 but a total is calculated. FIN_Count_NoPhysicianCharge is correctly calculating <blank> for 504 but a total is not calulated. I need a total so I can slice by other dims ie by Patient_Facility which currently will be blank because of the blank total. I have 2 versions of the calculation in the table and neither of them work. The explanation of the formula and measures are in the original post. i have spent 2 more days on this and still cant get this to work I have tried umpteen modifications on these measures. Any help will be appreciated as I am dead in the water for a week.
6 days later I figurerd this out myself. The original measure needed to be modified to this:
FIN_Count_NoPhysicianCharge_OLD = (CALCULATE(DISTINCTCOUNT(Revenue_Extract[FIN]), FILTER(values(Revenue_Extract[FIN]), ([Quantity_PhysicianCharges] <= 0 && ([Quantity_ADP] +[Quantity_PersonManagement]) > 0 && SUM(Revenue_Extract[Total_Charge_Amount]) > 0))))
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |