Sum of expense is wrong when multiple date selected

[Anonymous]

this is my pbix file : https://1drv.ms/u/s!Ag9tIyk2ofNRjk7ex9NVCSWmzVF9?e=hzuIkt

i have this measure:

Expenses = 
var NEWtotalrevenue = CALCULATE(SUM(Revenue[Total]),FILTER('Calendar','Calendar'[Date] >= DATE(2022,10,3)))
VAR OLDtotalrevenue = CALCULATE(SUMX(Revenue,Revenue[Amount]*Revenue[Qtty]),FILTER('Calendar','Calendar'[Date] < DATE(2022,10,3)))
VAR totalrevenue = OLDtotalrevenue + NEWtotalrevenue
Var newsalesmanrevenue = CALCULATE(SUM(Revenue[Total]),FILTER('Calendar','Calendar'[Date] >= DATE(2022,10,3)),Revenue[Salesman]="A")
VAR Oldsalesmanrevenue = CALCULATE(SUMX(Revenue,Revenue[Amount]*Revenue[Qtty]),FILTER('Calendar','Calendar'[Date] < DATE(2022,10,3)),Revenue[Salesman]="A")
VAR salesmanrevenue = Oldsalesmanrevenue + newsalesmanrevenue
VAR percentage = salesmanrevenue/totalrevenue
var expenses = SUM(Expense[Total])
return
expenses*percentage

those are the pictures:
expense table:

revenue table:

week 40  :

 week 41:

 

both selected weeks:

 

as you see expense on both selected dates is 801.01 
but if you manually add week 40 and week 41,
i get 365.30 + 431.27 = 796.57 
any help is appreciated

@Jihwan_Kim 

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood the question correctly, but please try the below measure whether it suits your requirement.

Expenses V2 =
VAR _newtable =
    ADDCOLUMNS (
        DISTINCT ( 'Calendar'[Week of Year] ),
        "@percentage", [Percentage V2],
        "@expensetotal", CALCULATE ( SUM ( Expense[Total] ) )
    )
RETURN
    SUMX ( _newtable, [@percentage] * [@expensetotal] )

[Anonymous]

Youre a life saver thank you