Calculate average age per category

[G_Whit-UK]

Hi,

I'm looking for a DAX / PBI Desktop based solution to calculate the average age of the items within a category.  The data set is such that we get a new transaction file every day, and the same transactions can appear on sucessive daily reports.  Therefore the age for each item being used by the calculation needs to be the maximum for that item, then the average for all the items within that category.  To make things more complicated, the report has a date slider, so the calculation needs to respect the selected date ranges.

Example data: Daily files appended into a single table:

Date	Trade Ref	Category	Column X	Column Y	Age
01/06/2023	1	A	x	y	0
01/06/2023	2	A	x	y	0
01/06/2023	3	B	x	y	0
01/06/2023	4	B	x	y	0
01/06/2023	5	A	x	y	0
02/06/2023	1	A	x	y	1
02/06/2023	2	A	x	y	1
02/06/2023	5	A	x	y	1
02/06/2023	6	B	x	y	0
02/06/2023	7	B	x	y	0
03/06/2023	2	A	x	y	2
03/06/2023	6	B	x	y	1
03/06/2023	8	B	x	y	0

(Column X & Y simply denote that the table has a number of other columns)

In my mind, the logic needs to be able to summarise the above table to list the distinct trade refs within the selected date range, then determine the maximum age of that transaction within the selected date range.  Once this temporary data has been derived, we can then calculate the overall average within each category using the maximum ages already calculated.

The tempory table I have in mind would look something like this:

Category	Trade Ref	Age
A	1	1
A	2	2
A	5	1
B	3	0
B	4	0
B	6	1
B	7	0
B	8	0

The output of the above would be as follows:

Distinct Count: A	3
Distinct Count: B	5
Avg Age: A	1.33
Avg Age: B	0.2

I've tried things like Summarize, SummarizeColumns (& some others), but I've not quite managed to get this to work.  Does anyone have any good ideas?  Thanks.

[v-shex-msft]

Hi @G_Whit-UK,

It seems like a common two-level aggregate calculation in Dax, you can try to use the following measure formula to get the average of age values based on current category group and max date value.

formula =
VAR summary =
    ADDCOLUMNS (
        SUMMARIZE (
            ALLSELECTED ( Table1 ),
            Table1[Category],
            "LDate", MAX ( Table1[Date] )
        ),
        "Age",
            LOOKUPVALUE (
                Table1[Age],
                Table1[Catetgory], [Catetgory],
                Table1[Date], [LDate]
            )
    )
RETURN
    AVERAGEX ( summary, [Age] )

Regards,

Xiaoxin Sheng

[G_Whit-UK]

Thanks @v-shex-msft , I think your suggestion is getting me in the correct direction, but it's still not quite there.

This is the current measure based on your earlier help:

Test Max Age = 
VAR summary =
    ADDCOLUMNS (
        SUMMARIZE (
            ALLSELECTED ('Global Failing Sales Data'),
            'Global Failing Sales Data'[Asset Type], 
            'Global Failing Sales Data'[Trade Ref], 
            'Global Failing Sales Data'[Region],
            "LDate", MAX ( 'Global Failing Sales Data'[Date] )
        ),
        "Age",
            LOOKUPVALUE (
                'Global Failing Sales Data'[Age],
                'Global Failing Sales Data'[Asset Type], [Asset Type],
                'Global Failing Sales Data'[Trade Ref], [Trade Ref],
                'Global Failing Sales Data'[Region], [Region],
                'Global Failing Sales Data'[Date], [LDate]
            )
    )
RETURN
    AVERAGEX ( summary, [Age] )

We are close in that if I select an individual record, the measure returns the correct value - example:

However, when looking at the ouput using a Matrix visualisation, each region  and asset type has the same value - which isn't correct (it is currently reflecting the overall avg reglardless of asset type and region):

What am I missing in the measure formula?  I appreciate your help.  Is it simply the case of somehow amending the "summary" variable?

[v-shex-msft]

Hi @G_Whit-UK,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[G_Whit-UK]

HI @v-shex-msft , I've attempted to respond to your last message twice now with details, but for some reason the responce is not being saved by the forum engine.

This is a brief version:  I am unable to supply data as I cannot attach files, and it is against company policy to use online drives (like Google Drive).  I'll see if I can give a more detail next week when I have time (3rd time lucky perhaps)...