DAX expression aggregation

[JB86]

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.

[v-zhangti · Accepted solution]

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.

[JB86]

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

[v-zhangti]

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.

[JB86]

Works exactly the way I needed. Thank you!

[gauthamboppana]

@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 !

[JB86]

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%