topic Re: Aging Totalization for Matrix Report in DAX Commands and Tips

Aging Totalization for Matrix Report

Anonymous — Thu, 26 Mar 2020 16:54:31 GMT

Hi, i am creating a Report about open orders and i want to create a matrix totallized by Division ( PLC) and after that by Age of the orders ( days from order to actual date).

The Age schema is the following [ 1-3/3-7/7-15/15-30/30-60/60-90/90+]

I have calculated the days from order date to todays date with this measure

I have created a measure that assign this Orders age schema

So the result in a normal table is this

But when i try to set it up in the Matrix visualization, Power Bi dont allow me to add the Agign result to the rows field so i cant set it up this way

How can i get the Measure Result to allow me do it?

Please help

Thanks in advance for your help

Re: Aging Totalization for Matrix Report

Stachu — Mon, 06 Apr 2020 20:09:44 GMT

in order to display something in rows it has to be in a column, cannot be in a measure. So basically you need a column that would have values like '90+ days', '60-90 days' etc.

Re: Aging Totalization for Matrix Report

Anonymous — Wed, 15 Apr 2020 06:52:18 GMT

Hi, Thanks for your response, finally i managed to set the days count from order date to todays date using the duration option at Edit Query/Transform son is not a measure anymore but still looking for translate the amount of days to the classification i need to totalize, any suggestion? I'm thinking to insert it from an excel file or create a conditional column with all the values, with this second option i don't get the expected result 1-3 3-7 7-15 15-30 30-60 60-90 90+ Thanks

Re: Aging Totalization for Matrix Report

Anonymous — Wed, 15 Apr 2020 11:55:28 GMT

You should have the labels of the classification stored in a dimension and connected the your fact table. Each order can be assigned one member from the dimension and this is how you slice data. The classification should be performed in Power Query.

Here's how to build correct and predictable models:

https://www.youtube.com/watch?v=_quTwyvDfG0

https://www.youtube.com/watch?v=78d6mwR8GtA

Best
D

Re: Aging Totalization for Matrix Report

Anonymous — Wed, 15 Apr 2020 16:17:04 GMT

Many thanks for your suggestions

Re: Aging Totalization for Matrix Report

Anonymous — Wed, 15 Apr 2020 16:26:04 GMT

Hi Again, i ave tried to create a Custom Colum with this configuration, but looks like i am missing something that dont allow me to apply it. Any Suggestion?

I also added ( and , to split each if and colse all ) at the end of the formula but same result

Re: Aging Totalization for Matrix Report

Stachu — Mon, 20 Apr 2020 04:52:42 GMT

it's missing else in the last if, you can change it from

you can remove the last row and add "90+" as else variant for the previous one

Re: Aging Totalization for Matrix Report

Anonymous — Mon, 20 Apr 2020 10:50:19 GMT

Many Many Many Thanks!!!! I was obsessed adding IFs hahahaha