cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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 )

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%