Forum Discussion
Helper IICalculate average age per category
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.
4 Replies
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-UKThanks Anonymous , 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?
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
