Column Subtotal Not Correct

[llnzheng]

I have a matrix like this:

The measure is Percent of Total Deal GCI Variance where I'm basically taking the percent of total deal GCI this year minus percent of total deal GCI from previous year:

Metric % Deal GCI Variance = DIVIDE (
    SUM ( 'Split Summary'[Deal GCI] ),
    CALCULATE (
        SUM ( 'Split Summary'[Deal GCI] ),
        ALLSELECTED ( 'Split Summary' )
    )
)
    - DIVIDE (
        SUM ( 'Split Summary'[Deal GCI (Previous Year)] ),
        CALCULATE (
            SUM ( 'Split Summary'[Deal GCI (Previous Year)] ),
            ALLSELECTED ( 'Split Summary' )
        )
    )

I'm suspecting the row totals are correct but not the column subtotals due to this measure using the ALLSELECTED() formula.  Is there any workaround to this?

[Anonymous · Accepted solution]

@llnzheng 

Hi, isnt that your expected outcome? Of the deals greater than $2m...there is a variance of -5.57%... resulting from a -0.77% in Cali, -1.31% in Southeast and so on?

Doing some SUMIFs in Excel seems to validate the measure results. 

[Anonymous]

Hi,
Based on the description, the DAX formula ahosakul  provided should be helpful.
Please view the method, if the method helps, please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Wisdom Wu

[saud968 · Accepted solution]

To resolve this, you can replace ALLSELECTED() with ALL(). This function removes all filters from the measure context, allowing for correct calculation of subtotals across all dimensions.

Revised Measure:

Metric % Deal GCI Variance =
DIVIDE (
SUM ( 'Split Summary'[Deal GCI] ),
CALCULATE (
SUM ( 'Split Summary'[Deal GCI] ),
ALL ( 'Split Summary' )
)
)
- DIVIDE (
SUM ( 'Split Summary'[Deal GCI (Previous Year)] ),
CALCULATE (
SUM ( 'Split Summary'[Deal GCI (Previous Year)] ),
ALL ( 'Split Summary' )
)
)

Explanation:

ALL( 'Split Summary' 😞 This removes all filters from the Split Summary table, ensuring that the SUM operations are performed across all rows, regardless of the current filter context.
Subtotals: With this modification, the subtotals will now calculate the percent of total deal GCI variance correctly, considering the entire dataset without the influence of row or column filters.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

[Anonymous · Accepted solution]

@llnzheng 

Hi, isnt that your expected outcome? Of the deals greater than $2m...there is a variance of -5.57%... resulting from a -0.77% in Cali, -1.31% in Southeast and so on?

Doing some SUMIFs in Excel seems to validate the measure results.