Sum of rows for max id in a group

[lokosrio]

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)

[v-chenwuz-msft · Accepted solution]

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.

[amitchandak]

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