Forum Discussion

questions's avatar
questions
Icon for Helper I rankHelper I
6 years ago
Solved

Summarize by max over multiple line and sum the value

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...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    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!

     

  • questions's avatar
    questions
    6 years ago

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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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's avatar
    questions
    Icon for Helper I rankHelper I
    6 years ago

    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's avatar
      v-kelly-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      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!