cancel
Showing results for
Did you mean:
Helper I

## Sum of rows for max id in a group

hi,

I have a problem with dax measure. I want to count the rows where the flag = 1, but only those rows where max_id column have biggest value in a group, which represents group_dimension. At the end I want to divide it by all rows considerd in the calculation.

Sample data:

 id dimension max_id group_dimension flag 1 dimension_999 123 dimension1 1 2 dimension_999 124 dimension1 0 3 dimension_999 125 dimension2 1 4 dimension_999 126 dimension2 0 5 dimension_999 127 dimension3 1 6 dimension_999 128 dimension3 1 7 dimension_999 129 dimension4 1 8 dimension_999 130 dimension4 1 9 dimension_999 130 dimension4 1

Final calculation for sample data:

Rows where flag = 1: 3

Rows considered in the calculation: 5

Result: 3/5 = 0,6

Please keep in mind that in the final table there will not be max_id and group_dimension columns, so I want to have aggregated result for other dimensions. What is more there are some filters, so it need to change dynamically.

I started with below formula, but it is not working for me:

``````var maxID =
MAXX (
ALLSELECTED ( sample_data ),
CALCULATE ( MAX ( sample_data [max_id] ), VALUES ( sample_data [group_dimension] ) )
)
return
CALCULATE(
COUNTROWS(sample_data ), sample_data [flag] = 1
, sample_data [max_id] = maxID)``````

1 ACCEPTED SOLUTION
Community Support

Hi @lokosrio,

Create two columns to mark this row if considered in calculation with 0/1.

First column:

Numerator =

VAR IF_max = CALCULATE(MAX('Table'[max_id]),FILTER('Table',[group_dimension]=EARLIER('Table'[group_dimension])))

return

IF(IF_max= 'Table'[max_id] &&'Table'[flag]=1,1,0)

Denominator =

VAR IF_considered = CALCULATE(MAX('Table'[Numerator]),FILTER('Table',[group_dimension]=EARLIER('Table'[group_dimension])))

return

if(IF_considered=1,1,0)

then create a measure:

Result = CALCULATE(DIVIDE(SUM('Table'[Numerator]),SUM('Table'[Denominator])),FILTER(ALL('Table'),'Table'[dimension]=MAX([dimension])))

The result:

With the measure, you can have aggregated result for other dimensions, it calculates dynamically.

Best Regards

Community Support Team _ chenwu zhu

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

2 REPLIES 2
Community Support

Hi @lokosrio,

Create two columns to mark this row if considered in calculation with 0/1.

First column:

Numerator =

VAR IF_max = CALCULATE(MAX('Table'[max_id]),FILTER('Table',[group_dimension]=EARLIER('Table'[group_dimension])))

return

IF(IF_max= 'Table'[max_id] &&'Table'[flag]=1,1,0)

Denominator =

VAR IF_considered = CALCULATE(MAX('Table'[Numerator]),FILTER('Table',[group_dimension]=EARLIER('Table'[group_dimension])))

return

if(IF_considered=1,1,0)

then create a measure:

Result = CALCULATE(DIVIDE(SUM('Table'[Numerator]),SUM('Table'[Denominator])),FILTER(ALL('Table'),'Table'[dimension]=MAX([dimension])))

The result:

With the measure, you can have aggregated result for other dimensions, it calculates dynamically.

Best Regards

Community Support Team _ chenwu zhu

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

Super User

@lokosrio , I doubt it it 3 by 5. I am getting only 2/4 with data you shared

check this

`` divide(sumx(VALUES(DT1[group_dimension]), LASTNONBLANKVALUE(DT1[id], sum(DT1[flag]))), DISTINCTCOUNT(DT1[group_dimension]))``
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Announcements