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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Surveys
Regular Visitor

DAX alternate value is not aggregating total

I have created the below matrix table. One of the measures created (LISA BG/AG Ratio) was returning blank values due to the measure being unable to divide by 0. I changed the DAX to provide an alternate value of 0. It seems that the alternate values are not aggregating with the other values in the column to provide an accurate Total amount (should be 0.8 not 2.3). I imagine this requires a little more advanced DAX than I have used. DAX syntax:

 

LISA BG/AG Ratio =
DIVIDE(
    SUM('FormCPEEndOfDay'[LISABelowGroundLeaks]),
    SUM('FormCPEEndOfDay'[LISAAboveGroundLeaks]),0
)

 

Any help is appreciated.

 

Surveys_0-1602594983465.png

 

1 ACCEPTED SOLUTION
dedelman_clng
Community Champion
Community Champion

Hi @Surveys  - can you explain further why you expect the "Total" row to show 0.8 instead of 2.3?  Your formula is BG/AG, which on the Total row is 25/11 = 2.3

 

PowerBI/DAX doesn't sum up the way Excel pivot tables do - measures are calculated in the context of the total data set - so it is acting correctly based on information you have given so far.

 

Hope this helps

David

View solution in original post

2 REPLIES 2
dedelman_clng
Community Champion
Community Champion

Hi @Surveys  - can you explain further why you expect the "Total" row to show 0.8 instead of 2.3?  Your formula is BG/AG, which on the Total row is 25/11 = 2.3

 

PowerBI/DAX doesn't sum up the way Excel pivot tables do - measures are calculated in the context of the total data set - so it is acting correctly based on information you have given so far.

 

Hope this helps

David

I was looking at it the wrong way. Thanks.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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