DAX Totals Not Summary

[james_98]

Hey guys,

hoping for a solution here (and not a link to another post)

I have the following DAX in this case the total aren't summing. Just wondering if there is anything wrong with the DAX, its working correctly at a line level.

Any help is appreciated 

Predicted GP ($) Impact =

VAR StartDate =

    LOOKUPVALUE (

        'Transaction Date'[Fiscal EndOfMonth],

        'Transaction Date'[*Date (trans)], DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 12, 1 )

    ) // Looking up 12 months ago as the start date to measure margin

VAR EndDate =

    LOOKUPVALUE (

        'Transaction Date'[Fiscal EndOfMonth],

        'Transaction Date'[*Date (trans)], DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 )

    ) // Don't include current month for analysis

VAR GP =

    CALCULATE (

        [Per Unit Gross Profit],

        'Transaction Date'[*Date (trans)] >= StartDate

            && 'Transaction Date'[*Date (trans)] <= EndDate

    ) // Standard GP calculation (before freight)

RETURN

    SUMX (

        VALUES ( 'Items Bridge'[Item Brand - ABV - Package Type] ),

        GP * [Variance to Budget]

    )

// Above GP per unit, multiplied by the volume miss/gain at an item level)

[DataNinja777]

Hi @james_98

Thanks for the clarification.  If you could share the pbix file with fake numbers, I can try to solve your problem.

[DataNinja777]

Hi @james_98,

Sumx function will force the addition to work for the yellow highlighted table in your formula.  If you'd like to use the total to work for other table/ fields, too, you need to include that table/fields too in the sumx.  

For example, if you want the subtotal to work for calendar table and product table for the multipled or divided amount which doesn't naturally add up, you need to wrap it up with sumx over the table you want to force the addition.  

[james_98]

thanks for your reply. i still think i am missing some understanding.
I tried adding the following, the items bridge table is what I want it to work with.
however still getting a signifcant variance from totals exported vs line level total

[DataNinja777]

Hi @james_98 

Is 'Items Bridge' a dimension table in your star schema data mode?  

[james_98]

Thanks @DataNinja777 

yes items bridge is basically the items dimension in the data model. 

gp per unit is a measure working off the fact table.