Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have this matrix,
I want to calculate profit in the "Total" line. To do that I need to display cost as a negative number. I was wondering if there is a way where I can write cost and revenue as positive numbers and then use Revenue - Cost to calculate profit and display it in total line?
One way that I can think of doing is to not display total line and create a low that does all the calculate as a part of table itself. I am looking for a better way if there is one.
Solved! Go to Solution.
MyMeasure =
IF (
HASONEVALUE ( Dim[lvl1] ),
SUM ( 'Table'[Value] ),
CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( Dim, Dim[lvl1] = "revenue" ) )
- CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( Dim, Dim[lvl1] = "cost" ) )
)
change the name here from total to profit:
Hi @tvaishnav ,
I have created a simple sample, please refer to it to see if it helps you.
Create a measure.
Measure =
IF (
ISINSCOPE ( 'Table'[Metric] ),
IF (
MAX ( 'Table'[Metric] ) = "Profit",
(
SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[HENGCOL] = SELECTEDVALUE ( 'Table'[HENGCOL] )
&& 'Table'[Metric] = "Revenue"
),
'Table'[value]
)
- SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[HENGCOL] = SELECTEDVALUE ( 'Table'[HENGCOL] )
&& 'Table'[Metric] = "Cost"
),
'Table'[value]
)
),
SUM ( 'Table'[value] )
)
)
If I have misunderstood your meaning, please provide more details with your desired output.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for your response @sturlaws. I thought about this but I don't think this will work. Revenue and cost belong to metric column. I want to change the row total, Profit to be different between Revenue and Cost.
Profit = Revenue - Cost
I do not want to present cost as a negative number. What are my options?
MyMeasure =
IF (
HASONEVALUE ( Dim[lvl1] ),
SUM ( 'Table'[Value] ),
CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( Dim, Dim[lvl1] = "revenue" ) )
- CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( Dim, Dim[lvl1] = "cost" ) )
)
change the name here from total to profit:
Thank you for your response. I will look into this and update here.
Hi,
yes, you can use the hasonevalue()-function in your formulas.
Assuming that the hiearchy in your matrix is like this: metric_level_1, metric_level_2
rewrite your measure something like this:
EstimateNew = if(hasonevalue(metric_level_1),[revenue]-[Cost],[Estimate])
Cheers,
Sturla
If this post helps, then please consider Accepting it as the solution. Kudos are nice too.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |