topic DAX Totals Not Summary in DAX Commands and Tips

DAX Totals Not Summary

james_98 — Mon, 04 Sep 2023 02:20:19 GMT

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)

Re: DAX Totals Not Summary

DataNinja777 — Mon, 04 Sep 2023 03:08:27 GMT

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.

Re: DAX Totals Not Summary

james_98 — Mon, 04 Sep 2023 03:17:05 GMT

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

Re: DAX Totals Not Summary

DataNinja777 — Mon, 04 Sep 2023 03:23:43 GMT

Hi james_98

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

Re: DAX Totals Not Summary

james_98 — Mon, 04 Sep 2023 03:32:23 GMT

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.

Re: DAX Totals Not Summary

DataNinja777 — Mon, 04 Sep 2023 04:32:00 GMT

Hi james_98

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