Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
ReneeR
Frequent Visitor

SUMX summing correctly but not producing grand total (blank total)

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.

 

 

FINQuantity_ADPQuantity_PersonManagementQuantity_PhysicianChargesTotal_Charge_AmountNoPhysicianCharge_COLFIN_Count_NoPhysicianChargeFIN_Count_NoPhysicianCharges_OLD
xxxxx-1 2-277FALSE  
xxxxx6  5460TRUE11
        
total5 25183  1
1 ACCEPTED SOLUTION
ReneeR
Frequent Visitor

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))))

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

here is a slimmed down data source:

 

Activity_DateFINTotal_Charge_AmountTotal_QuantityActivity_TypePatient_Facility
2/7/20185049101Admit/Transfer/DischargeBrownDeer
2/9/20185043261Physician ChargesBrownDeer
2/9/20185049101Admit/Transfer/DischargeBrownDeer
2/8/20185049101Admit/Transfer/DischargeBrownDeer
2/7/20185039101Admit/Transfer/DischargeTampa
2/6/20185039101Admit/Transfer/DischargeTampa
2/8/20185039101Admit/Transfer/DischargeTampa
2/9/20185039101Admit/Transfer/DischargeTampa

 

Here are expected results (mocked up table visualization):

 

FINQuantity_ADPQuantity_PersonManagementQuantity_PhysicianChargesTotal_Charge_AmountFIN_Count_NoPhysicianCharge_OLDFIN_Count_NoPhysicianCharge
5034  364011
5043 130561 (should be blank) 
TOTAL7 166962 (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.

 

ReneeR
Frequent Visitor

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))))

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.