Subtotal without calculation

[Sreerams26]

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. 

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[v-zhangti]

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.

Solved: wrong total of measure - Microsoft Power BI Community

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]

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]

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.