Calculating The Average in dynamic way

[youssefm9]

Hi All,

I am trying to calculate subtotal average for the below. 

In the below table I have in each row the total MRR (month recurring revenue) for each month. If I want to calculate the MRR per client and per group, I simply divide the MRR by Client or Group. In that way I am calculating the average for each month. When I select in the filter a specific month, the figures will give correct result.

However, if I want to calculate the overall average when I select more than 1 month, I need to sum up all MRR for the selected months and divide by the number of the selected months and then divide by the average number of group or client.

Would appreciate your support.

[Anonymous · Accepted solution]

Hi  @youssefm9 ,

I created some data:

Here are the steps you can follow：

1. Create calculated table.

Slicer =
DISTINCT('Table'[Month_Year])

2. Create measure.

Client_Measure =
var _select=SELECTCOLUMNS('Slicer',"1",[Month_Year])
var _count=COUNTX(ALLSELECTED('Slicer'),[Month_Year])
var _selectsum=SUMX(FILTER(ALL('Table'),'Table'[Month_Year] in _select),[MRR])
return
IF(
    HASONEFILTER('Table'[Month]),
    [Average per Client],
    DIVIDE(
    DIVIDE(
        _selectsum ,_count),    AVERAGEX(FILTER(ALLSELECTED('Table'),'Table'[Month_Year] in _select),[Clients])))

Group_Measure =
var _select=SELECTCOLUMNS('Slicer',"1",[Month_Year])
var _count=COUNTX(ALLSELECTED('Slicer'),[Month_Year])
var _selectsum=SUMX(FILTER(ALL('Table'),'Table'[Month_Year] in _select),[MRR])
return
IF(
    HASONEFILTER('Table'[Month]),
    [Average per Group],
    DIVIDE(
    DIVIDE(
        _selectsum ,_count),    AVERAGEX(FILTER(ALLSELECTED('Table'),'Table'[Month_Year] in _select),[Groups])))

3. Result:

Best Regards,

Liu Yang

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

[Greg_Deckler]

@youssefm9 First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

[youssefm9]

Hi @Greg_Deckler ... thanks but not what i was looking for.