Summarize by max over multiple line and sum the value

[questions]

I want to cacluate the % of qty submitted out of total qty.

I have multipule line for bill no. 1 in my dataset due to data issue. However, whenever there are date appear in any line, I will consider bill no. 1 as submitted.  Thus, my expected result is 200/300 = 66.7% but now in the measure I made, it is showing 150/300 = 50%. Can anyone have any clue how the measure should be written?

Bill no.	Submitted Date	qty
1	1/12/2019	50
1		30
1		20
2	3/12/2019	100
3		100

[v-kelly-msft · Accepted solution]

Hi @questions ，

You need a measure as below:

Measure = 
var a =IF(MAX('Table'[Submitted Date])<>BLANK()&&MAX('Table'[Index])<>1,CALCULATE(SUM('Table'[qty]),FILTER(ALL('Table'),'Table'[Index]<=SELECTEDVALUE('Table'[Index]))),BLANK())
var b = SUMX(ALL('Table'),'Table'[qty])
Return
a/b

Finally you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[v-kelly-msft · Accepted solution]

Hi @questions ，

You need a measure as below:

Measure = 
var a =IF(MAX('Table'[Submitted Date])<>BLANK()&&MAX('Table'[Index])<>1,CALCULATE(SUM('Table'[qty]),FILTER(ALL('Table'),'Table'[Index]<=SELECTEDVALUE('Table'[Index]))),BLANK())
var b = SUMX(ALL('Table'),'Table'[qty])
Return
a/b

Finally you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@questions ,

Try like

measure =
var _tab = summarize(Table[Bill No], "_Date",firstnonbank(table[Submitted Date]),"_qty",sum(table[Qty])
)
return
divide(sumx(_tab,if(not(isblank[_Date])),[_qty],0),sumx(_tab,[_qty]))

[questions · Accepted solution]

Thanks!! it works but i just finetuning a bit so it works out...Here it is in case other users need this information.

measure =
var _tab = summarize(Table[Bill No], "_Date",firstnonbank(table[Submitted Date],0),"_qty",sum(table[Qty])
)
return
divide(sumx(_tab,if(not(isblank([_Date])),[_qty],0),sumx(_tab,[_qty]))

[v-kelly-msft]

Hi @questions ,

Good job,so is your issue solved?Can you mark the reply as answered to close it?

Much appreciated.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!