Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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!
Hi @lingvistt
Please can you create a sample data and provide the DAX expressions that you are currently using?
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.
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!
User | Count |
---|---|
94 | |
78 | |
73 | |
63 | |
60 |
User | Count |
---|---|
108 | |
100 | |
77 | |
63 | |
61 |