We have two tables
Table A =
Dates = CALENDAR(date(2023,1,1),TODAY())
Edits ADDED LINE ITEM MANGO Table B =
Date joined to Date from Data table, One to Many.
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
OR
February
You will get the Output just by using the below code
roi = divide(sum(data[spent]),sum(data[return]))
However, it won't give you the output which needs to include the Kiwi line item for January and Mango line item for February.
NEW EDIT THAT INCLUDES NEW DAX THAT I TRIED WHICH GIVES CLOSER RESULTS TO EXPECTED OUTPUT. The below section is Added 3/29 10 AM PST, the above section was the original question
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
Added +0 at the end, which gives a closer output format to expected output, but the results for Pear are wrong, which should've been 8 for January and 0 for February.
Try this measure:
ROIZ =
IF (
MAX ( Data[Type] )
IN VALUES ( Data[Type] ),
DIVIDE (
SUM ( Data[Returns] ),
SUM ( Data[Spent] )
) + 0,
BLANK ()
)