Forum Discussion

Sreerams26's avatar
Sreerams26
Frequent Visitor
3 years ago

Subtotal without calculation

I have two tables.  First table has product and warehouse wise price for various dates in current year and second table has the product and warehouse wise quantity as on date.  I had created a measure to pickup the latest date using the below DAX code in the first table - 

Latest Price =
VAR LatestDate =
   CALCULATE(
      MAX('Item price'[Pricecreateddatetime]),
      ALLEXCEPT('Item price', 'Item price'[Unique item])
   )
RETURN
   CALCULATE(
      MAX('Item price'[Price]),
      FILTER(
         'Item price',
         'Item price'[Pricecreateddatetime] = LatestDate
      )
   )
 
Then, calculated the inventory value in the same table using 
Inventory Value = [Latest Price]*SUM('On hand Inventory'[OnHandQuantity]).
 
Finally, I create a matrix table.  The materials are grouped by category.  Hence, the matrix table brings in data by category.  Individual data is perfect however the sub total is not showing the sum of inventory value. rather it is taking the highest price in that group and multiplying with the total qty.  In this eg. total qty in the group is 30 and multiplying with highest value 95.87.  however, the actual total of all inventory value is 2344.20.
 

How do I ensure that the total displayed is actually the sum of all items. 

Need Help

4 Replies

  • v-zhangti's avatar
    v-zhangti
    Community Support
    3 years ago

    Hi, Sreerams26 

     

    I simulated some data to hopefully match your situation. Please mainly look at the method.

    [Lastest Price] is a measure.

    Measure = SUM('Table'[Physical Inventory])*[Lastest Price]
    Inventory Value = IF(HASONEVALUE('Table'[Unique item]),[Measure],SUMX('Table',[Measure]))

    IF(HASONEVALUE()) can control the output of total.
     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Sreerams26's avatar
      Sreerams26
      Frequent Visitor
      3 years ago

      First of all, thanks for the response.  I am not sure if I did it correctly, still I am not getting the right response. Based on your suggestion, I made the necessary changes in the formula. The total in Group ASIA is reconciling howerver, none of the other groups match.  So wondering what could be missing?

       

      Inv Value Measure = [Latest Price]*SUM('On hand Inventory'[OnHandQuantity])
      Inventory Value = if(HASONEVALUE('Item price'[Unique item]),[Inv Value Measure], SUMX('Item price',[Inv Value Measure]))
      • v-zhangti's avatar
        v-zhangti
        Community Support
        3 years ago

        Hi, Sreerams26 

         

        Sample data:

        The formula does not change.

        If your results are inconsistent with expectations, can you provide more data?

         

        Best Regards,

        Community Support Team _Charlotte

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.