Forum Discussion

G_Whit-UK's avatar
G_Whit-UK
Icon for Helper II rankHelper II
3 years ago

Calculate 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 sam...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
G_Whit-UK
Icon for Helper II rankHelper II
3 years ago

Thanks 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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      G_Whit-UK
      Icon for Helper II rankHelper II
      3 years ago

      HI Anonymous , 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)...