Forum Discussion

bipowerbix's avatar
bipowerbix
Helper I
3 years ago
Solved

Tricky Situation with SUM

Here is the Data I am using:

Type               Spent   Returns 

Apple100    
Orange      50200
Banana25150
Pear 500
Kiwi  

 

If I use 

 

 

 

 

ROI = sum(Data[Returns]) / sum(Data[Spent])

 

 

 

 

 

I get the following

 

Information on Data:

the spent and returned data is filled in by the user and will remain blank until then. So will have to create DAX measures that will assign 0 where there is a blank. However, if we do that, we get Infinity and NaN for Pear or Apple. So we have to HardCode such incidents to 0.

 

Questions

 1)  I also want to show the Kiwi line item as well and assign 0 to the ROI column for Kiwi. 

 2) How to deal with Infinity, show 0 instead of Infinity.

3) Result in either 1 of the shown expected output will work

 

Any suggestions, tips, and tricks are welcome.

 

I want to get the following output along with totals at the end for the first two columns and the average for the third column, shown below:

Expected Output

Type   Spent   Returns  ROI

Apple100  
Orange502004
Banana251506
Pear 500 
Kiwi   
Total1758505

 

OR

 

Type   Spent   Returns  ROI

Apple100 0
Orange502004
Banana251506
Pear 5000
Kiwi  0
Total1758505