Re: DAX Totals Not Summary

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

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

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

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)

james_98 Tracker

Re: DAX Totals Not Summary

Re: DAX Totals Not Summary

DAX Totals Not Summary