Forum Discussion
New measure while modeling shows blank rows
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?
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.
3 Replies
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
Community Champion
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.
