cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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.

1 ACCEPTED SOLUTION
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

2 REPLIES 2
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

Regular Visitor

I was looking at it the wrong way. Thanks.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors