Is there a way to use custom formula for matrix grand total?

[tvaishnav]

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.

[v-rongtiep-msft]

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.

[tvaishnav]

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?

[sturlaws · Accepted 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:

[tvaishnav]

Thank you for your response. I will look into this and update here.

[sturlaws]

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.