DAX Total is not working

[Aazam]

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

[tamerj1 · Accepted solution]

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 )
        )
)

[tamerj1 · Accepted solution]

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 )
        )
)

[Aazam]

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.

[Aazam · Accepted solution]

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
       
)

[Aazam]

Thank you for the solution 🙂

Suggest me the learning material for DAXs

[tamerj1]

@Aazam 
The Definitive Guide to DAX - SQLBI

[eliasayyy]

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))

[Aazam]

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

[eliasayyy]

try afater the emasure

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