How to sum up the max values in the totals and subtotals

[LamSar]

Good morning,

I have a question:
I created a Matrix with Location, productID, Date (most recent date that interacts with date slicer), and the Stock Quantity on that specific day.

The values on the rows are correct. However, the subtotals and totals show the max Value of the specific day based on the whole matrix.

The total on the second level is showing zero. That should be 500.

The grand total is showing 10, that should also be 500.

The measure I usesd is this: 

CALCULATE(MAX('stock'[StockHoeveelheid]),FILTER('stock', 'stock'[date] = MAX('stock'[date]

)))

Any ideas?

[amitchandak]

@LamSar , refer if the measure in these two can help

https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...

[LamSar]

Hello,

These workarounds did not work for me.

For each location and article I need to retain the most recent value, based on the selection of the date slicer. 

[v-zhangti]

Hi, @LamSar 

You can try the following methods.

Measure = 
Var _maxdate=CALCULATE(MAX(data1[Date]),ALLEXCEPT(data1,data1[Location],data1[Article]))
Return
IF(HASONEVALUE(data1[Date]),SUM(data1[Aantal]),CALCULATE(MAX(data1[Aantal]),FILTER(ALL(data1),[Date]=_maxdate)))

Result = IF(HASONEVALUE(data1[Article]),[Measure],MAXX(data1,[Measure]))

Is this the result you expect?

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.

[LamSar]

Hello,

No this is not the result I expect.

The Total for location BK should be 527:

The Total for location cl92: 1041

The grand Total: 1568

Alsof the matrix van only show one date per product: that is the most recent date with a stockvalue based on the date slicer.

[v-zhangti]

Hi, @LamSar 

You can try the following methods.

Result = 
IF ( HASONEVALUE ( 'data1'[Article] ), [Measure],
    SUMX (
        SUMMARIZE ( 'data1', data1[Location], 'data1'[Article], "Subtotal", [Measure] ),
        [Subtotal]
    )
)

Is this the result you expect?

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.

[LamSar]

When I try your solution on the real dataset, it doesn't seem to work.

[v-zhangti]

Hi, @LamSar 

Can you provide sample files and expected results?

Best Regards

[LamSar]

Hello,

You can find the anonymized file here: 
https://1drv.ms/u/s!AuaqHTxLCZ_h9yeFyzfvX4q1Ew3U?e=gP1DjT

The excpected results:

When end date in date slicer is on 30/06/2022, I expect the following results:

The things that are marked in yellow are correct. 

The things in red are incorrect. 

The subtotal for Stock Qty should be as follows:

- pk_location 11: 29

- pk_location 12 : 1041

The grand total for Stock Qty should be:  1070

For Purchase Price/ Unit (not subtotals and totals are needed)

For Stock Value: 

the Subtotals for Stock Value should be:

pk_location 11: 11,07

pl_location 12: 35.52

Th grand total  for Stock  Value should be: 46,59

When end date in date slicer is on 01/08/2022, I expect the following results:

The things that are marked in yellow are correct. 

The things in red are incorrect. 

The subtotal for Stock Qty should be as follows:

- pk_location 11: 154

- pk_location 12 : 1167

The grand total for Stock Qty should be:  1321

For Purchase Price/ Unit (not subtotals and totals are needed)

For Stock Value: 

the Subtotals for Stock Value should be:

pk_location 11: 58,81

pl_location 12: 83,64

Th grand total  for Stock  Value should be: 142,45