Forum Discussion
SUM OF FIRST ROWS OF EVERY GROUP
Hi Team,
Newbie here,
May I ask if it is possible to create a DAX expression the will RESULT on like below image.
The raw data are TYPE ATTRIBUTE and SECONDS
RESULT should be the sum of first rows in SECONDS column per TYPE and ATTRIBUTE.
Thank for assisting! Really appreciate the big help! 🙂
Anonymous ,
You need in index column for that - https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi
After that add a new column in DAX
new column =
var _1 = minx(filter(Table, [ATTRIBUTE] = earlier([ATTRIBUTE])),[index])
var _2 = calculate(sumx(distinct(Table[SECONDS]),[SECONDS]),filter(Table, [ATTRIBUTE] = earlier([ATTRIBUTE])))
return
if( [index] =_1, _2, blank())Hi Anonymous ,
First create a Index column, then create three measures
Rank1 = CALCULATE(MIN('Table'[Index]),ALLEXCEPT('Table','Table'[Attribute],'Table'[Type]))Rank2 = CALCULATE ( MIN ( 'Table'[Index] ), ALLEXCEPT ( 'Table', 'Table'[Attribute] ) )Result = IF ( MAX ( 'Table'[Index] ) = [Rank2], CALCULATE ( SUM ( 'Table'[Seconds] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[Attribute] ), [Index] = [Rank1] ) ), 0 )You can check more details from here.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- amitchandak
Super User
Anonymous ,
You need in index column for that - https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi
After that add a new column in DAX
new column =
var _1 = minx(filter(Table, [ATTRIBUTE] = earlier([ATTRIBUTE])),[index])
var _2 = calculate(sumx(distinct(Table[SECONDS]),[SECONDS]),filter(Table, [ATTRIBUTE] = earlier([ATTRIBUTE])))
return
if( [index] =_1, _2, blank()) Hi Anonymous ,
First create a Index column, then create three measures
Rank1 = CALCULATE(MIN('Table'[Index]),ALLEXCEPT('Table','Table'[Attribute],'Table'[Type]))Rank2 = CALCULATE ( MIN ( 'Table'[Index] ), ALLEXCEPT ( 'Table', 'Table'[Attribute] ) )Result = IF ( MAX ( 'Table'[Index] ) = [Rank2], CALCULATE ( SUM ( 'Table'[Seconds] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[Attribute] ), [Index] = [Rank1] ) ), 0 )You can check more details from here.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.