Incorrect Subtotal

[juhoneyighot]

Hello,

 Anyone who knows why I get an incorrect result for the Total of Rev Recog for each _Current PM Contact (sample for Benton Graves). This is form PowerBI result

Rev Recog Formula is = Est Rev* % Comp

Total for Rev Recog for Benton Graves must be 7,809,514.68

I'm just using this for the total and not any measure

Please guide me trhough this.

Thank you.

[Anonymous · Accepted solution]

Hi @juhoneyighot ,

This is a very common problem, usually the total rows are correct for the measure, just not what most people expect. This is because the column totals for these rows are not calculated based on the sum of the results in the rows, but are calculated using the same measure formula and applied to the sum of the selected data.

You can create a secondary measure as needed that applies to the default totals calculation. Based on your description, I created the following example.

Measure3 =
VAR _product = 'financials'[Measure] * [Measure 2]
VAR _total =
    SUMX ( ALLSELECTED ( financials[Product] ), [Measure] * [Measure 2] )
RETURN
    IF (
        ISINSCOPE ( financials[Product] ),
        _product,
        IF ( ISINSCOPE ( financials[Discount Band] ), _total )
    )

In this example I used the ISINSCOPE function to do different calculations for different levels of data in the matrix. Of course, there are other functions that can solve the problem, see the link for more information:

Measure Totals, The Final Word - Microsoft Fabric Community

Why my measure returns the wrong total? How to fix that? (vahiddm.com)

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[juhoneyighot]

@Anonymous 
I have tried your ideas and it works. Since your formula is only until per _Current PM Contact, I've inserted a formula so it shows the total per _JobPhase. And this works.
Rev Recog =
VAR _project = [Est Rev] * [% Comp]
VAR _total =
SUMX ( ALLSELECTED ( msdyn_project[Name] ), [Est Rev] * [% Comp] )
VAR _total2 =
SUMX ( ALLSELECTED ( msdyn_project[Name] ), [Est Rev] * [% Comp] )
RETURN
IF (
ISINSCOPE ( msdyn_project[Name] ),
_project,
IF ( ISINSCOPE (msdyn_project[_CurrentPM contact] ),
_total,
IF ( ISINSCOPE (msdyn_project[_JobPhase] ),
_total2)
))

However, the overall total for all projects doesn't show. What formula should I insert on the formula above showing the grand/over total?

[Anonymous]

Hi @juhoneyighot ,

If your matrix has no more layers, you can write the calculation of the total number of items in the third argument of the last if function. Its logic is similar to the previous one and you can write your own to suit your situation.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.