Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
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]))
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.
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]))
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]))
Hi @questions ,
Good job,so is your issue solved?Can you mark the reply as answered to close it?
Much appreciated.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
107 | |
69 | |
68 | |
43 |
User | Count |
---|---|
148 | |
103 | |
103 | |
88 | |
66 |