Forum Discussion

Aazam's avatar
Aazam
Icon for Helper I rankHelper I
3 years ago
Solved

DAX Total is not working

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 CA...
  • tamerj1's avatar
    tamerj1
    3 years ago

    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's avatar
    Aazam
    3 years ago

    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's avatar
Aazam
Icon for Helper I rankHelper I
3 years ago

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's avatar
Aazam
Icon for Helper I rankHelper I
3 years ago

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
       
)