New measure while modeling shows blank rows

[lingvistt]

Hi all,

I have a table with activities of customers, profit and cost for each activity.

Based on 'Table 1' I calculated new measure 'ROI'='Profit'/'Cost'. Then I want to group activities using additional table and modelling (see screenshot) and see the results of ROI in pivot by customers and group of activity.

The problem is that: when I create a matrix with 'Customer' (Table 1), 'Group of Activities' (Table 2) and 'ROI' (Calculated Measure), I see all groups of activities for all customers (I mean that for some customers several activities are blank as there these activities didn't exit, and the problem is that matrix shows them; but I want to see only those groups of activities, which were held with customers).

First, I though that I had many-to-many relationship between tables, but not, it is single, and in matrix I still see blank activities. But now I think that in this case I shall somehow rewrite the ROI measure formula. Could you help me on that?

[Mariusz]

Hi @lingvistt 

Please can you create a sample data and provide the DAX expressions that you are currently using?

Best Regards,
Mariusz

Please feel free to connect with me.

[lingvistt]

Here is the sample Data. The DAX expression: ROI = SUM(Table1[Profit])/SUM(Table1[Cost])+1

When I was creating sample, I got the root for the problem: in formula I use '+1' (the logic is if ROI>100%, than it's fine). If I eliminate '+1', it works perfectly, but in this case I do not get what I want.

P.S. I wanted to attach sample or pbix, but I think it is not permitted to do it here. Only photos.

[Mariusz · Accepted solution]

HI @lingvistt 

Try the Measure below. 

Measure = 
VAR _div = DIVIDE( SUM( 'Table'[Profit] ), SUM( 'Table'[Cost] ) )
RETURN IF( NOT ISBLANK( _div ), _div + 1 ) 

Hope this helps!

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.