Search code examples
powerbidaxcalculated-columns

How to show line items having null values using DAX measure


We have two tables 

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

Edits ADDED LINE ITEM MANGO Table B = 

enter image description here

Relationship between columns: enter image description here

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:

enter image description here

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

When I select February, I get the following:

enter image description here

which is wrong, pear should show nothing or 0


Expected Output: (January) enter image description here

OR 

February

enter image description here


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.

Jan Output enter image description here

Feb Output enter image description here


Solution

  • Try this measure:

    ROIZ =
    IF (
        MAX ( Data[Type] )
            IN VALUES ( Data[Type] ),
        DIVIDE (
            SUM ( Data[Returns] ),
            SUM ( Data[Spent] )
        ) + 0,
        BLANK ()
    )