Forum Discussion
Solved
Total Divided by Unique ID count
Hi All, Do i need to write this is SQL or is it possible as a meassure in Power Bi. I need to take the total payload result being the Last record of weight type B 98.5 and devide it by the co...
Hi Freeseman ,
Here are the steps you can follow：
1. Create measure.
Measure = var _maxdate= MAXX(FILTER(ALL('Table'),'Table'[HAUL_CYCLE_REC_IDENT]=MAX('Table'[HAUL_CYCLE_REC_IDENT])&&'Table'[Group]<>"T") ,[TIMESTAMP]) var_sum= SUMX(FILTER(ALL('Table'),'Table'[HAUL_CYCLE_REC_IDENT]=MAX('Table'[HAUL_CYCLE_REC_IDENT])&&'Table'[TIMESTAMP]=_maxdate),[PAYLOAD]) var _count= COUNTX(FILTER(ALL('Table'), 'Table'[HAUL_CYCLE_REC_IDENT]=MAX('Table'[HAUL_CYCLE_REC_IDENT])&&'Table'[TIMESTAMP]<_maxdate),[Group]) return DIVIDE( _sum,_count)2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi Freeseman ,
Try the code below to see if it works for you.
Max Value =
VAR vMaxTime =
CALCULATE ( MAX ( 'Table'[DateTime] ), FILTER ( 'Table', 'Table'[B/T] <> "T" ) ) //What is the max time not related to T
VAR vRecordCount =
CALCULATE (
COUNTROWS ( 'Table' ),
ALLEXCEPT ( 'Table', 'Table'[Ident] ),
FILTER ( 'Table', 'Table'[B/T] <> "T" )
) //What is the record count
VAR vMaxValue =
CALCULATE (
MAX ( 'Table'[Payload] ),
ALLEXCEPT ( 'Table', 'Table'[Ident] ),
FILTER ( 'Table', 'Table'[DateTime] = vMaxTime && 'Table'[B/T] <> "T" )
) //What is the maxvalue
RETURN
DIVIDE ( vMaxValue, vRecordCount )
Did I help you today? Please accept my solution and hit the Kudos button.