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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

DAX expression aggregation

Hello community,

I am working with daily records stored mostly as durations in seconds. I am using DAX to DIVIDE numerators and denominators to return percentage values. Once I put these values into a matrix and aggregate by associate by week begin date the values sum to the level of aggregation which I do not want. I want the DIVIDE to continue throughout all levels. How can I solve for this? Below is an example of the data and what I am trying to accomplish.

1 ACCEPTED SOLUTION
Community Support

Hi, @JB86

You can try the following methods.

Column:

``value = IF([numerator]=0,0,DIVIDE([numerator],[denominator]))``

Measure:

``````Measure =
IF (
HASONEVALUE ( 'Table'[report_date] ),
SUM ( 'Table'[value] ),
DIVIDE ( SUM ( 'Table'[numerator] ), SUM ( 'Table'[denominator] ) )
)
``````

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 REPLIES 6
Community Support

Hi, @JB86

You can try the following methods.

Column:

``value = IF([numerator]=0,0,DIVIDE([numerator],[denominator]))``

Measure:

``````Measure =
IF (
HASONEVALUE ( 'Table'[report_date] ),
SUM ( 'Table'[value] ),
DIVIDE ( SUM ( 'Table'[numerator] ), SUM ( 'Table'[denominator] ) )
)
``````

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

In the measure, what does 'Table'[Value] represent in your example?

Community Support

Hi, @JB86

'Table'[Value] is the percentage of your above table.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

Works exactly the way I needed. Thank you!

Solution Specialist

@JB86  - If you want to look at weekly values, its better to create a calculated column in your table for week start date and then  use it in the Matrix (instead of the report date column).

WeekStart=[report_date] - WEEKDAY ([report_date], 3 )

Did I answer your question? Mark my post as a solution! If not, please feel free to ask me.

Also, I would ❤ Kudos if my solution helped.  It is a token of appreciation!

Thank you very much !

Frequent Visitor

Thank you for the assistance! I had another question come from trying to return values for the current week where the value returned is calculating much higher than it should be. Would this calc column solve for that?
Example: None of these values should be higher than 100%

Helpful resources

Announcements

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Power BI Monthly Update - May 2024

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

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors