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]))  