Sales Return percentage

[Itzsridhar]

Hi There,

I have a table data which has sales and sales return both in the same sheet, but i have a column named sales type there i have diffrentiated each row as sales or sales return. Now my question is how do i get the sum (returns)/Sum(sales) from this !

Thanks in advance !!

[v-yangliu-msft · Accepted solution]

Hi  @Itzsridhar ,

I created some data:

Here are the steps you can follow：

1. Create calculated column.

Month = MONTH('Sheet1'[Date])

2. Create measure.

Measure =
var _select=SELECTEDVALUE('Sheet1'[Month])
var _sales=
SUMX(FILTER(ALL('Sheet1'),'Sheet1'[sales type]="sales"&&'Sheet1'[Month]=MAX('Sheet1'[Month])),'Sheet1'[Net Value])
var _reurn=
SUMX(FILTER(ALL('Sheet1'),'Sheet1'[sales type]="return"&&'Sheet1'[Month]=MAX('Sheet1'[Month])),'Sheet1'[Net Value])
return
DIVIDE(_reurn,_sales)

3. Result:

The slicer is 1:

The slicer is 2:

If I understand something wrong, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data

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.

[v-yangliu-msft]

Hi  @Itzsridhar ,

I created some data:

Here are the steps you can follow：

1. Create measure.

Measure =
var _sales=
SUMX(FILTER(ALL('Sheet1'),'Sheet1'[sales type]="sales"),'Sheet1'[Net Value])
var _reurn=
SUMX(FILTER(ALL('Sheet1'),'Sheet1'[sales type]="return"),'Sheet1'[Net Value])
return
DIVIDE(_reurn,_sales)

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.

[Itzsridhar]

@v-yangliu-msft  thanks for sharing your solution, it gives the value for whlole data availalbe in the sheet. when i change the filter along with that this retrun% doesnt change since we have divded the entire value.. i need the return % to be changed based on the filters i make.. 

Thanks!

[amitchandak]

@Itzsridhar , Try a measure like

divide(calculate(sum(Table[sales]), filter(Table, table[sales type] ="return")), calculate(sum(Table[sales]), filter(Table, table[sales type] ="Sales")))

or

divide(calculate(sum(Table[sales]), filter(all(Table), table[sales type] ="return")), calculate(sum(Table[sales]), filter(all(Table), table[sales type] ="Sales")))

[Itzsridhar]

Hi @amitchandak  !!

THanks for your reply have tried with your solution, got this error

Too many arguments