topic Re: DAX Total is not working in DAX Commands and Tips

DAX Total is not working

Aazam — Wed, 16 Aug 2023 08:43:26 GMT

Hi,

I am facing an issue with DAX Total,

Here is my DAX
item cost for max date = var MaxDate = CALCULATE(MAX('Lookup Table'[transaction_date]),ALL('Lookup Table'[transaction_date])) RETURN
CALCULATE(SUMX('Lookup Table','Lookup Table'[Item Cost]),FILTER('Lookup Table','Lookup Table'[transaction_date] = MaxDate))

Look at this, It is getting only that value is on MAX date instead of a total of both product

Re: DAX Total is not working

eliasayyy — Wed, 16 Aug 2023 08:30:39 GMT

not sure why you are using sumx

item cost for max date = var MaxDate = CALCULATE(MAX('Lookup Table'[transaction_date]),ALL('Lookup Table'[transaction_date])) RETURN CALCULATE(SUM('Lookup Table'[Item Cost]),FILTER('Lookup Table','Lookup Table'[transaction_date] = MaxDate))

Re: DAX Total is not working

Aazam — Wed, 16 Aug 2023 08:41:01 GMT

Look at this, It is getting only that value is on MAX date instead of a total of both product

Re: DAX Total is not working

tamerj1 — Wed, 16 Aug 2023 12:32:32 GMT

Hi Aazam
Please try

item cost for max date = SUMX ( SUMMARIZE ( 'Lookup Table', 'Lookup Table'[Item Code], 'Lookup Table'[Item Category], 'Lookup Table'[SKu], 'Lookup Table'[Location Name] ), VAR MaxDate = CALCULATE ( MAX ( 'Lookup Table'[transaction_date] ), ALL ( 'Lookup Table'[transaction_date] ) ) RETURN CALCULATE ( SUMX ( 'Lookup Table', 'Lookup Table'[Item Cost] ), KEEPFILTERS ( 'Lookup Table'[transaction_date] = MaxDate ) ) )

Re: DAX Total is not working

eliasayyy — Wed, 16 Aug 2023 14:10:43 GMT

try afater the emasure

Total =
IF(HASONEVALUE([Item Code]),[Item Cost For Max Date] , SUMX(VALUES([Item Code]),[Item Cost For Max Date]))

Re: DAX Total is not working

Aazam — Thu, 17 Aug 2023 08:35:54 GMT

Thank you for the solution 🙂

Suggest me the learning material for DAXs

Re: DAX Total is not working

tamerj1 — Thu, 17 Aug 2023 09:39:57 GMT

Aazam
The Definitive Guide to DAX - SQLBI

Re: DAX Total is not working

Aazam — Thu, 17 Aug 2023 11:33:24 GMT

I have another question related to this,

Total_Cost_Max is the multiplication of item_cost_for_max_date and item_on_hand

Total_Cost_MAX = [item cost for max date] * [Item_On_Hand_SUM]

But in total I want the sum of rows instead of the multiplication of columns.

Re: DAX Total is not working

Aazam — Thu, 17 Aug 2023 12:12:44 GMT

I resolved it by myself

SUMX ( SUMMARIZE ( 'Lookup Table', 'INV Sku'[sku], 'INV Categories'[name], 'INV Items'[item_code], 'INV Locations'[location_name] ), VAR item_cost_for_max_date = CALCULATE ( [item cost for max date] ) VAR item_on_hand = CALCULATE( [Item_On_Hand_SUM] ) RETURN item_cost_for_max_date * item_on_hand )