Apply an average to a measure sub-total - rather than applying the measure logic to the sub-total

[gaznez]

We have a measure that counts the number of individual customers we've traded with for each month.  At 'year' level - the measure will count the number of individual customers we've traded with for the year as a whole.  Rather than do this - is there any way I can get the year total to be an average of the 12 months in the year?  This would be the only meaningful KPI to the users of the report.

My dax for the measure is 

CustCount = countrows(filter(CustomerMaster, [TotalSales]>0))

Any help would be much appreciated! Thank you!

[gaznez]

Thanks @audreygerred - I managed to get the desired result using AVERAGEX (and some help from Chat GPT!!)

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.  Show the expected result as well.

[Anonymous]

Hi @gaznez ,

Here some steps that I want to share, you can check them if they suitable for your requirement.

Here is my test data:

1.Create a measure as you have provided and create another measure to calculate average of year

CustCount = countrows(filter(CustomerMaster, [TotalSales]>0))

Average = 
VAR T = CustomerMaster[CustCount]
RETURN
IF(
    ISFILTERED(CustomerMaster[Year]),
    T,
    T/DISTINCTCOUNT(CustomerMaster[Month])
)

2.Final output

Best regards

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[gaznez]

Hi @Anonymous ,

I tried your suggestion, but it didn't quite work (see below)...

The CustCount was my original formula - and the AvgCustCount is your suggestion.  This AvgCustCount has taken the 711 for the year (which in this case is the number of individual customers we've traded with in the year)  and divided it by 12 - giving 59.  My users are looking an average of the individual 12 months - which using the above numbers would be 367.  Any further suggestions...?

Thanks for your help!

[Anonymous]

Hi  @gaznez ,
Thank you for your reply, based on the screenshot you provided I don't quite understand how you calculated this 711, I'm not sure if you have any other table relationships or how your matrix was created. Here are my modifications to my previous MEASURE, I hope it helps.

Averge_count = 
VAR _table = SUMMARIZE(CustomerMaster,CustomerMaster[Customer],"CostCount",[CustCount])
VAR _total = SUMX(_table,[CostCount])
RETURN
IF(
    ISFILTERED(CustomerMaster[Year]),
    _total,
    _total/DISTINCTCOUNT(CustomerMaster[Month])
)

Final output

If the above steps don't satisfy you, could you please provide the pbix file that you tested. Please remove any sensitive data in advance.

Best regards

Albert He

If this post helps, then please consider Accept it as the solutionto help the other members find it more quickly

[gaznez]

Thanks for your suggestions.  The 711 was the unique number of customers that traded with us throughout the year (as several customers buy in each month - and so will only be counted once at the year level).  I managed to get the desired result using AVERAGEX.

[audreygerred · Accepted solution]

You can try AVERAGEX - this function will iterate through each row to calculate what you need at the row level then average them for the total: AVERAGEX function (DAX) - DAX | Microsoft Learn