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

     

8 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    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
      Icon for Helper I rankHelper I
      3 years ago

      Thank you for the solution 🙂

      Suggest me the learning material for DAXs

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

         

  • eliasayyy's avatar
    eliasayyy
    Icon for Memorable Member rankMemorable Member
    3 years ago

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

       

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

      • eliasayyy's avatar
        eliasayyy
        Icon for Memorable Member rankMemorable Member
        3 years ago

        try afater the emasure

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