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.

Reply
JB86
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.

 

JB86_0-1658497686561.png

JB86_1-1658497755293.png

 

1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community Support

Hi, @JB86 

 

You can try the following methods.

Column:

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

vzhangti_0-1658821743161.png

Measure:

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

vzhangti_1-1658821813859.png

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.

View solution in original post

6 REPLIES 6
v-zhangti
Community Support
Community Support

Hi, @JB86 

 

You can try the following methods.

Column:

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

vzhangti_0-1658821743161.png

Measure:

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

vzhangti_1-1658821813859.png

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.

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

v-zhangti
Community Support
Community Support

Hi, @JB86 

 

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

vzhangti_0-1659073397602.png

 

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.

 

Works exactly the way I needed. Thank you!

gauthamboppana
Solution Specialist
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 !



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%

JB86_0-1663763057988.png

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors