DAX Combinations not working as intended.

[bipowerbix]

We have two tables 

Table A =  

Dates = CALENDAR(date(2023,1,1),TODAY())

Table B = 

Type        Cost   Return  Date

Apple	100		1/1/2023
Orange	50	200	1/5/2023
Banana	25	150	2/1/2023
Pear		500	2/22/2023
Kiwi			1/25/2023
Pear	50	400	1/5/2023

Relationship between columns:

Date joined to Date from Data table, One to Many.

Example 1:

DAX to get ROI 

ROIZ = 
VAR V1 = CALCULATE(SUM(Data[Spent]),ALLSELECTED(Dates[Date]),ALLEXCEPT(Data,Data[Type]))
VAR V2 = CALCULATE(SUM(Data[Returns]),ALLSELECTED(Dates[Date]),ALLEXCEPT(Data,Data[Type]))
return divide(V2,V1,0)

When I select January, I get the following:

which is wrong, pear should show 8 instead of 18, and Kiwi is missing.

When I select February, I get the following:

which is wrong, pear should show nothing or 0

Expected Output: (January)

Type    Spent  Returns ROI     Date

Apple	100	null	0	1/1/2023
Orange	50	200	4	1/5/2023
Kiwi	null	null	0	1/25/2023
Pear	50	400	8	1/5/2023
total	200	600	6

OR 

Expected Output February

Type     Spent   Return  ROI    Date

Banana	25	150	6	2/1/2023
Pear	null	500	0	2/22/2023
Total	25	650	6

[bipowerbix]

ROIZ = 
VAR V1 = CALCULATE(SUM(Data[Spent]),ALLSELECTED(Dates[Date]),ALLEXCEPT(Data,Data[Type]))
VAR V2 = CALCULATE(SUM(Data[Returns]),ALLSELECTED(Dates[Date]),ALLEXCEPT(Data,Data[Type]))
return divide(V2,V1,0) +0

Tried this to get Kiwi, however, the Pear shows the wrong amount, 18 instead of 8

[Greg_Deckler]

@bipowerbix Ditch the CALCULATE statements, as in most cases, they are are not needed:

ROIZ NC = 
    VAR __V1 = SUM('Data'[Cost])
    VAR __V2 = SUM('Data'[Return])
    VAR __Result = DIVIDE(__V2, __V1)
RETURN
    __Result

[bipowerbix]

Hi Greg, Thanks for the reply, Yes I was using Calculate() to Solve for Kiwi. Still don't see Kiwi in the result. I want to show the line items that don't have data as well, as shown in expected output

ROI9 = 
VAR V1 = SUM(Data[Spent])
VAR V2 = SUM(Data[Returns])
VAR V3 =  divide(V2,V1)
RETURN V3