Minimum value subtotal based on specific dates

[sunah132]

Hi,

I'm trying to add subtotal with the minimum value and the subtotal is also taking a minimum of the total price.

If I want to apply value as a minimum and sum up them into the subtotal, what would be a good way?

Thank you in advance.

item	5/1/2021	5/2/2021	5/3/2021	Total	Expected
1	100	100	100	100	300
2	150	150	150	150	450
3	200	200	200	200	600

In this one, the total should be 912.8 as expected.

Thank you.

[Jihwan_Kim]

Hi, @sunah132 

Please try something like below.

Min Value measure =
SUMX ( VALUES ( 'Table'[Date] ), MIN ( 'Table'[Value] ) )

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[sunah132]

Thank you so much!

That measure applied on my report successfully.

If I would like to calculate row subtotal, would it be using same as this Min Value measure =
SUMX ( VALUES ( 'Table'[Date] ), MIN ( 'Table'[Value] of drill up value ) ) ?

[Jihwan_Kim]

Hi, @sunah132 

Thank you for your feedback.

Sorry that I quite do not understand your last question.

Please describe how you like to see the row subtotal.

[sunah132]

Sorry, I meant to explain this below. I applied your suggestion that had total value on the column but rows still appear to have a minimum value. 

[Jihwan_Kim · Accepted solution]

Hi, @sunah132 

Thank you for sharing.

Please try the below.

Min Value measure =
SWITCH (
TRUE (),
ISINSCOPE ( 'Table'[item] ), SUMX ( VALUES ( 'Table'[Date] ), MIN ( 'Table'[Value] ) ),
SUM ( 'Table'[Value] )
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[sunah132]

Hello, Thank you for your suggestion and It worked very well.

I faced the problem that there was no pricing made on a certain date. It resulted in a total of 0 while they bring summing up the minimum values. Could you please see how it should be?

I appreciate your help!

[Jihwan_Kim]

Hi, @sunah132 

Please try the below.

Min Value measure =

SWITCH (

TRUE (),

ISINSCOPE ( 'Table'[item] ), SUMX ( VALUES ( 'Table'[Date] ), CALCULATE( MIN ( 'Table'[Value] )) ),

SUM ( 'Table'[Value] )

)

[sunah132]

Thank you! I have one last question regarding the Matrix calculation. When there's drill down like this, adding Calculate at SUMX would sum up the entire Hub but not in route level. I have tried adding SUM('Table'[Value]) as route but the visual failed to do so. 

[Jihwan_Kim]

Hi, @sunah132 

Thank you for your feedback.

Please try to replace "isinscope" with "isfiltered".

And try to include the proper column inside the isfiltered DAX function.

[sunah132]

Thank you so much! It was very helpful.👍