cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Display most recent values in matrix subtotals instead of sum/avg/max/min etc.

I have data that includes prices at specific dates for different subcategories (customer/location/product/product subcategory)

 

I want to always show the most recent price in the subtotal, no matter what level I've expanded the matrix to. But my only options are sum/avg/max/min/etc. 

 

The subtotals always show the max or sum or average and not the actual most recent price. In the example below, I want to show 0.1800 as the most recent price, not .2200 because it is the max. 

 

Is that possible with the matrix visual? Some way to tie it to the date field next to it? 

 

tylerwws_0-1593210819033.png

 

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Resolver IV
Resolver IV

Re: Display most recent values in matrix subtotals instead of sum/avg/max/min etc.

Hi @tylerwws,

 

Could you not do something like this

 

VAR MaxDate = MAX( Table[Your Lower Level Date Column] )

RETURN

CALCULATE(

    MAX( Table[Price] ),

    Table[Your Lower Level Date Column] = MaxDate

)

 

The variable finds the maximum date in the context and then we take the price on that date. At the lowest date level the maximum date will always be the date itself and in the subtotal the variable.

 

Let me know how you get on.

 

Kris

View solution in original post

Highlighted
Resolver IV
Resolver IV

Re: Display most recent values in matrix subtotals instead of sum/avg/max/min etc.

Hi @tylerwws,

 

You need to create a new measure not a new column to do this. There's large restrictions on the functions you can use in a calculted column when using Direct Query but using CALCULATE in a measure is definitely allowed.

 

Let me know how you get on!

 

Kris

View solution in original post

5 REPLIES 5
Highlighted
Resolver IV
Resolver IV

Re: Display most recent values in matrix subtotals instead of sum/avg/max/min etc.

Hi @tylerwws,

 

Could you not do something like this

 

VAR MaxDate = MAX( Table[Your Lower Level Date Column] )

RETURN

CALCULATE(

    MAX( Table[Price] ),

    Table[Your Lower Level Date Column] = MaxDate

)

 

The variable finds the maximum date in the context and then we take the price on that date. At the lowest date level the maximum date will always be the date itself and in the subtotal the variable.

 

Let me know how you get on.

 

Kris

View solution in original post

Highlighted
Super User III
Super User III

Re: Display most recent values in matrix subtotals instead of sum/avg/max/min etc.

@tylerwws 

 

I created some sample date.

1.PNG

 

Please try below measure

measure = 
VAR maxdate=CALCULATE(MAX('Table (2)'[date]),ALL('Table (2)'))
return CALCULATE(MAX('Table (2)'[price]),FILTER(ALLEXCEPT('Table (2)','Table (2)'[product]),'Table (2)'[date]=maxdate))

2.PNG

Hope this is helpful.

 

Did I answer your question? Mark my post as a solution.Appreciate your Kudos!
Proud a to be a Datanaut!
Thanks and BR
Ryan
Highlighted
Regular Visitor

Re: Display most recent values in matrix subtotals instead of sum/avg/max/min etc.

But I do want it to show the date specific info at the lowest drill down level. So in your example, the rows for 'a' and 'b' should say 3 and 6, but the other rows should still list the specific prices for that date. Does that make sense? 

Highlighted
Regular Visitor

Re: Display most recent values in matrix subtotals instead of sum/avg/max/min etc.

@kriscoupe

I'm getting this error - seems this might not work with Direct Queries? Do you know if there is a way around that?

"'CALCULATE' is not allowed as part of calculated column DAX expression on DirectQuery modes."

 

tylerwws_0-1593437778565.png

 

Highlighted
Resolver IV
Resolver IV

Re: Display most recent values in matrix subtotals instead of sum/avg/max/min etc.

Hi @tylerwws,

 

You need to create a new measure not a new column to do this. There's large restrictions on the functions you can use in a calculted column when using Direct Query but using CALCULATE in a measure is definitely allowed.

 

Let me know how you get on!

 

Kris

View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors