Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tylerwws
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
kriscoupe
Responsive Resident
Responsive Resident

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

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
ryan_mayu
Super User
Super User

@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!

Proud to be a Super User!




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? 

kriscoupe
Responsive Resident
Responsive Resident

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

@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

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.